Table Insert

Introduction

The Table Insert operator is used to insert rows in a heap table, and optionally (especially in a narrow update plan) in one or more nonclustered indexes as well; or to insert rows in a memory-optimized table and all its nonclustered indexes. 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 Table Insert operator is fully equivalent to the Clustered Index Insert operator when used on heap tables. When used on memory-optimized tables there are likely some internal optimizations that are not documented and not exposed in the execution plan.

Visual appearance in execution plans

Depending on the tool being used, a Table Insert operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Table 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 in a heap table, the Table 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 Table 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 Table Insert can hold one or more values. The first is always the name of a heap table. If there are more, then the rest are all nonclustered indexes on that table, and rows inserted to the table are “simultaneously” inserted in those nonclustered indexes as well. This is typical for a narrow update plan, and always the case when the insert target is a memory-optimized table.

Return row

Like any operator, Table 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 table 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 Table 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 heap table that the Table Insert operator will insert rows to, using three part naming (database, schema, table). Optionally, one or more additional values may be supplied; these are nonclustered indexes on the same table, specified using four part naming (database, schema, table, index). The subproperties of the Object property represent the three or four name parts separately, but also includes some additional properties: GraphWorkTableIdentifier: This property is used to distinguish between multiple sets of graph worktables, in execution plans that include more than one occurrence of the SHORTEST_PATH evaluation pattern (described in detail here]). Only used for graph worktables. GraphWorkTableType: This property is used to identify the type of graph worktables. Supported values are 0 (input data for the current iteration), 1 (input data for the next iteration), 2 (all data encountered so far), 3 (results so far), and 4 (source data for the first iteration). Only used for graph worktables. Index Kind: Represents what kind of index data is inserted into. For a Table Insert, the Index Kind is Heap on the first Object if it is a heap table, or the Index Kind property is not present if it is a memory-optimized table or a graph worktable. If extra nonclustered indexes are listed, they will have Index Kind set to NonClustered or NonClusteredHash. Storage: Determines the storage type of the index. For a Table Insert, this can be either RowStore or MemoryOptimized, and it is always the same for all Objects.
Partitioned	This property is present and set to True when the target of the Table Insert is a partitioned table.
Predicate	Maps columns from the input stream to the columns in the table 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 Table 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 Table Insert operator.

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