Columnstore Index Update
Introduction
The Columnstore Index Update 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 either an Index Update or a Clustered Index Update operator.
SQL Server currently supports three types of index storage: rowstore, columnstore, and memory-optimized. Indexes of each of those types can be the target of an Index Update or a Clustered Index Update, as indicated by the Storage subproperty of the Object property. When the Storage property is RowStore or MemoryOptimized, then the normal Index Update or a Clustered Index Update icon is used, but when Storage is ColumnStore, and only a single index is listed in the Object property, then Management Studio and Plan Explorer show a different icon instead. (Azure Data Studio does show the regular icon for Index Update or a Clustered Index Update in these cases).
While clustered and nonclustered indexes each have their own operator for rowstore storage, these types are lumped together as a single icon and operator name in SSMS. Whether the targeted columnstore index is a clustered or a nonclustered index is only visible in parentheses behind the operator name (Clustered vs Nonclustered), or in the Logical Operation property (Clustered Index Update vs Index Update).
Visual appearance in execution plans
Depending on the tool being used, an Index Update or a Clustered Index Update operator with just a single update target that has its Storage property equal to ColumnStore is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
(shown as Index Update or Clustered Index Update) |
Algorithm
The Columnstore Index Update is not a real operator but merely a visual indicator that an Index Update or a Clustered Index Update operator is modifying data in a columnstore index. Please check the appropriate pages for a full description of the algorithms of those operators.
Input or Seek Predicates
The generic description of the Clustered Index Update operator shows that the rows to be modified can be either provided by a child operator, or be defined in a Seek Predicate property. For the (nonclustered) Index Update, only the former method is supported.
A Columnstore Index Update also can only read rows from a child operator; a Seek Predicate property is not supported. This is true even when the target columnstore index is the clustered index. This makes sense when you realize that, due to their storage structure, seek operations are not possible on columnstore indexes.
The input rows will always contain a “ColStoreLocnnnn” input, which is a single internal value that represents 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 Update 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 Index Update operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| ActionColumn | When this property is present, it lists the name of the column from the input that determines for each input row whether it updates existing data, inserts new data in the index, or deletes an existing row from the index. When this property is missing, all input rows are considered to be an update to an existing row. |
| DMLRequestSort | When set to true, the update operation 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 Update 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:
|
| Partitioned | When this property is present and set to true, the target of the Columnstore Index Update 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. |
| WithOrderedPrefetch | As far as currently known, prefetching is never used (and hence this property never included) for a Columnstore Index Update operator. |
Implicit properties
This table below lists the behavior of the implicit properties for the Columnstore Index Update operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Columnstore Index Update operator supports row mode execution only. |
| Blocking | The Columnstore Index Update operator is non-blocking. |
| Memory requirement | The Columnstore Index Update operator does not have any special memory requirement. |
| Order-preserving | The Columnstore Index Update operator is fully order-preserving. |
| Parallelism aware | The Columnstore Index Update 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 Update operator is not segment aware. |