Columnstore Index Merge

Introduction

The Columnstore Index Merge is not really an actual operator. You can encounter it in graphical execution plans in SSMS (and other tools), but if you look at the underlying XML of the execution plan, you will see that it is actually a Clustered Index Merge operator.

SQL Server currently supports two types of index storage for clustered indexes: rowstore and columnstore. (The third storage type, memory-optimized, supports nonclustered indexes only). Clustered indexes of both types can be the target of a Clustered Index Merge, as indicated by the Storage subproperty of the Object property. When the Storage property is RowStore, then the normal Clustered Index Merge icon is used. But when Storage is ColumnStore, then Management Studio and Plan Explorer show a different icon instead. (Azure Data Studio does show the regular icon for a Clustered Index Merge in these cases).

Visual appearance in execution plans

Depending on the tool being used, a Clustered Index Merge operator that has its Storage property equal to ColumnStore is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan
			(shown as Clustered Index Merge)

Algorithm

The Columnstore Index Merge is not a real operator but merely a visual indicator that a Clustered Index Merge operator is modifying data in a columnstore index. Please check the appropriate page for a full description of the algorithm of this operator.

Action Column and Original Action Column

As described on the Clustered Index Merge page, the Action Column property lists a column that specifies, for each input row, whether it represents an update, a delete, or an insert. However, the index structure of a columnstore index does not allow for in-place updates; each logical update to a columnstore index is always performed by the engine as a delete of the original data and an insert of the new data. This modification from update to delete + insert is not done internally inside the operator, but in the execution plan.

If the merge might have to perform any logical updates, then the optimizer will insert a Split operator to change each row that calls for an in-place update into two rows, for a delete and an insert. If needed it will also add a Sort operator to ensure all deletes are done before any inserts; this ensures that even intermediate states can never have a duplicate value caused by inserting a new version of a row before deleting the old version, as such an intermediate state could cause run-time errors in the maintenance of other indexes.

For some operations, such as for instance deciding whether the modification should be visible in the inserted and deleted pseudo-tables of an update trigger or those of an insert or delete trigger, the operator does still need to know when the original, logical modification was an update. The Original Action Column property lists the column where this data is stored. This column can, if needed, also be returned to the parent operator.

ColStoreLocnnnn

The input rows to the Columnstore Index Merge will always contain a “ColStoreLocnnnn” column. For rows that represent an insert, this column is not used; for rows that represent a delete it is a single internal value, representing the location of the row to be deleted. This “columnstore locator” is actually a combination of two values: the rowgroup number, and the ordinal position of the row within that rowgroup.

Operator properties

The properties below are specific to the Columnstore Index Merge operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Index Insert operator are marked with a *.

(Note that most of these properties are exactly the same as for the Clustered Index Merge operator; they are repeated here for ease of use).

Property name	Description
ActionColumn	This property lists the name of the column from the input that determines for each input row whether it inserts new data in the index or deletes an existing row from the index.
DMLRequestSort	When set to true, the modification might qualify for minimal logging if additional conditions apply. Details of those additional conditions and the requirements for DMLRequestSort to be true can be found here.
Object	The index that the Colummnstore Index Merge operator will modify rows in, using four part naming (database, schema, table, index). The subproperties of the Object property represent the four name parts separately, but also include these additional properties: Index Kind: Represents what kind of index data is targeted. For a Columnstore Index Merge, this is always Clustered. OnlineInbuildIndex: This property is present and set to 1 when a concurrent online index operation is running. The actual target is the temporary new index structure that is being built by an Online Index Insert operator. Storage: Determines the storage type of the index. For a Columnstore Index Merge, this is always ColumnStore.
Original Action Column	This property lists the name of the column from the input that describes the logical type of modification. If a logical update has been split into a delete and an insert (see main text), then the original action column will represent both of those rows as parts of a logical update.
Partitioned	When this property is present and set to true, the target of the Columnstore Index Merge is a partitioned index.
Predicate	Maps columns from the input stream to the columns in the index indicated by the Object property, or sets these columns to hardcoded values or variables from the query text.

Implicit properties

This table below lists the behavior of the implicit properties for the Columnstore Index Merge operator.

Property name	Description
Batch Mode enabled	The Columnstore Index Merge operator supports row mode execution only.
Blocking	The Columnstore Index Merge operator is non-blocking.
Memory requirement	The Columnstore Index Merge operator does not have any special memory requirement.
Order-preserving	The Columnstore Index Merge operator is fully order-preserving.
Parallelism aware	The Columnstore Index Merge operator does not support parallelism. It can only be used in a serial plan, or in a serial section of a parallel plan.
Segment aware	The Columnstore Index Merge operator is not segment aware.