Columnstore Index Insert

Introduction

The Columnstore Index Insert 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 Insert or a Clustered Index Insert 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 Insert or Clustered Index Insert, as indicated by the Storage subproperty of the Object property. When the Storage property is RowStore or MemoryOptimized, then the normal icon for (Clustered) Index Insert 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 Insert or Clustered Index Insert 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 Insert vs Index Insert).

Visual appearance in execution plans

Depending on the tool being used, an Index Insert or Clustered Index Insert operator with just a single insert target 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 Index Insert or Clustered Index Insert)

Algorithm

The Columnstore Index Insert is not a real operator but merely a visual indicator that an Index Insert or Clustered Index Insert operator is inserting data in a columnstore index. Please check the appropriate pages for a full description of the algorithms of those operators.

Operator properties

The properties below are specific to the Columnstore Index Insert 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 Insert operator; they are repeated here for ease of use).

Property name	Description
DMLRequestSort	When set to true, the insert 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 Insert operator will insert rows to, 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 inserted into. For a Columnstore Index Insert, this can be ViewNonClustered (for nonclustered columnstore indexes on an indexed view), NonClustered (for all other nonclustered columnstore indexes), or Clustered (for all clustered columnstore indexes). 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 Insert, this is always ColumnStore.
Partitioned	This property is present and set to True when the target of the Columnstore Index Insert is a partitioned index.
Predicate	Maps columns from the input stream to the columns in the columnstore index specified in the Object property, or sets these columns to hardcoded values or variables from the query text.
WithUnorderedPrefetch	This property is present (and set to true) when prefetching is requested and order doesn’t need to be preserved. Exact details of prefetching in the context of a Columnstore Index Insert operator are unknown at this time. The assumed effect of prefetching is that the operator issues an asynchronous request to the storage system to insert each row, then immediately continues with the next. Rows are returned to the parent operator in the order in which the storage system completes the requests, which might be different from the order in which the rows are read by the operator. When this property is not present, no prefetching is used.

Implicit properties

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

Property name	Description
Batch Mode enabled	The Columnstore Index Insert operator supports row mode execution only.
Blocking	The Columnstore Index Insert operator is non-blocking.
Memory requirement	The Columnstore Index Insert operator does not have any special memory requirement.
Order-preserving	The Columnstore Index Insert operator is fully order-preserving when no prefetching is used. When the WithUnorderedPrefetch property is present and true, then the Columnstore Index Insert operator is not order-preserving.
Parallelism aware	The Columnstore Index Insert 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 Insert operator is not segment aware.