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:
SQL Server Management Studio
Azure Data Studio
(version 17.4 and up)
(until version 17.3)
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.
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.
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.
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.
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.
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).
|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 include two additional properties:
|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.
This table below lists the behavior of the implicit properties for the Table Insert operator.
|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.|