Clustered Index Insert
Introduction
The Clustered Index Insert operator is used to insert rows in a clustered index, and optionally (especially in a narrow update plan) in one or more nonclustered indexes as well. The rows to be inserted can be produced by the operator’s child subtree, or can be defined in the operator’s properties. After inserting a row, the operator returns data from that row to its parent.
The Clustered Index Insert operator is in most ways identical in functionality to the (nonclustered) Index Insert operator. One key difference, though, is that a Clustered Index Insert can optionally insert data in more than one index, when multiple objects are specified in its Object property. Even though in that case the operator targets both clustered and nonclustered indexes, it is still represented as a Clustered Index Insert in the execution plan.
A Clustered Index Insert operator that targets a clustered columnstore index and no additional nonclustered indexes is represented in SSMS (and most other tools) as a Columnstore Index Insert operator in the graphical execution plan. To match the representation of the execution plan that most people use, I’ll focus the descriptions below on how Clustered Index Insert operates when the Storage property is ‘RowStore’, and call out relevant differences on the separate Columnstore Index Insert page.
Visual appearance in execution plans
Depending on the tool being used, a Clustered Index Insert operator (except when it has a single Object with 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 |
 |
 |
 |
 |
Algorithm
The basic algorithm for the Clustered Index Insert operator is as shown below:
Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.
Acquire locks
Before inserting data, the Clustered Index Insert operator first acquires the necessary locks (or verifies it already has them). Which locks exactly are taken depends on the isolation level of the transaction, but it is never possible to completely eliminate all locking on data modifications. If the operator has additional nonclustered indexes in its Object property, then it acquires the locks for each index when it is ready to insert data in that index.
This operator does not release the locks it takes, nor does any other operator in the same execution plan. Locks for data modifications are always held until the end of the transaction.
A full discussion of locking strategies used for the various transaction isolation levels is beyond the scope of this website.
Insert row
The Clustered Index Insert operator doesn’t do any constraint checking. The optimizer ensures that foreign key and check constraints are checked if the new data might violate them. Primary keys and unique constraints are not explicitly checked; SQL Server creates a unique index for each such constraint, and the storage engine ensures an error is raised as soon as data is written to the index that would cause a duplicate entry.
More objects?
The Object property of the Clustered Index Insert can hold one or more values. The first is always a clustered index. If there are more, then the rest are all nonclustered indexes on the same table, and rows inserted to the clustered index are “simultaneously” inserted in those nonclustered indexes as well. This is typical for a narrow update plan.
Return row
Like any operator, Clustered Index Insert is invoked by calling its GetNext() method. And like any other operator, it responds my returning a row, with the columns listed in the Output List property. Most of the time these will be some or all of the values just inserted, although additional columns from the input that are not inserted into the index may also be passed on.
These output rows can be used by parent operators for additional logic, such as constraint checking or propagating the changes to other indexes, indexed views, etc.
Operator properties
The properties below are specific to the Clustered 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 first object listed is the clustered index that the Clustered Index Insert operator will insert rows to, using four part naming (database, schema, table, index). Optionally, one or more additional values may be supplied; these are nonclustered indexes on the same table. They, too, are specified using four part naming. The subproperties of the Object property represent the four name parts separately, but also include these additional properties:
|
| Partitioned | This property is present and set to True when the target of the Clustered Index Insert is a partitioned index. |
| Predicate | Maps columns from the input stream to the columns in the clustered 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 Clustered 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 Clustered Index Insert operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Clustered Index Insert operator supports row mode execution only. |
| Blocking | The Clustered Index Insert operator is non-blocking. |
| Memory requirement | The Clustered Index Insert operator does not have any special memory requirement. |
| Order-preserving | The Clustered Index Insert operator is fully order-preserving when no prefetching is used. When the WithUnorderedPrefetch property is present and true, then the Clustered Index Insert operator is not order-preserving. |
| Parallelism aware | The Clustered 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 Clustered Index Insert operator is not segment aware. |