The Table Merge operator is used when rows in a heap table need to be changed, and the required changes can be a combination of inserts, updates, and deletes. Optionally (especially in a narrow update plan) a Table Merge can affect one or more nonclustered indexes as well, though this is currently limited to rowstore indexes only. If a nonclustered columnstore index exists on the heap, then rows in this index are always maintained in a separate Columnstore Index Update operator. The rows to be affected, as well as the required action and the new values of all columns in the index, are produced by the operator’s child subtree. After performing a modification, the operator returns data from that row to its parent; this data can be both the original or the new data.
The Table Merge operator has, as far as currently known, no support for memory-optimized tables.
The Table Merge operator is in most ways identical in functionality to the Index Update operator when it has its Action Column property set, except that it updates data stored in a heap table. One key difference, though, is that a Table Merge can optionally specify one or more nonclustered indexes on the target table in its Object property, and then the data in those indexes will be changed simultaneously. Even though in that case the operator targets both a heap and one or more nonclustered indexes, it is still represented as a Table Merge in the execution plan.
Visual appearance in execution plans
Depending on the tool being used, a Table Merge 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)
(Yes, the icons for the “old” and “new” versions of SQL Server Management Studio look exactly the same)
The basic algorithm for the Table Merge 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 modifying data, the Table Merge operator will always acquire the necessary locks (or verify that it already has them). Which locks exactly are taken depends on the isolation level of the transaction, as well as the action column. It is never possible to completely eliminate all locking on data modifications.
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 Merge operator uses the input columns listed in its Action Column property to specify, per individual row, what type of modification is needed (insert, update, or delete).
The known values and their meaning for the action column (for any operator, not just for Table Merge) are:
- 1: Update
- 3: Delete
- 4: Insert
It is currently unknown whether there are more possible values.
The value in the action column determines the type of modification for the heap table, but also for each nonclustered index if additional nonclustered indexes are listed in the Object property. If an update changes a value in a key column in a nonclustered index, then it is not known whether the internals of this operator or the internals of the storage engine transform the update into a delete of the old key and an insert of the new key.
When the action column indicates that a row needs to be inserted, the Table Merge operator uses the Predicate property with SetPredicateType equal to “Insert” to find the values for all columns in that row. The Table Merge operator does not actively check any constraints. Primary keys and unique constraints are implicitly checked because the storage engine throws an error as soon as data is written to a unique index that would cause a duplicate entry. For other constraints, the optimizer adds additional operators to gather relevant data, then checks the validity of the data in an Assert operator.
When the action column indicates that a row needs to be updated, the Table Merge operator finds the row to be updated in the heap by using the file, page and row number in the “row identifier” (RID) that is passed in in one of the input columns, typically named Bmknnnn where nnnn is a four-digit number that is unique within the execution plan. It then uses the Predicate property with SetPredicateType equal to “Update” to find the columns to update and the new values to assign to those columns.
For each additional nonclustered index specified in the Object property, the operator uses the values in its key columns in the original row, as retrieved from the existing heap entry, to read the existing index row. It then updates the value of each column in that index that also appears in the Predicate property with SetPredicateType equal to “Update” to the indicated new value. If this modifies one or more of the key values of the nonclustered index, then the logical update cannot be done in place; the existing entry in its old location is deleted, and a new entry is inserted in the new location.
There might be situations where the optimizer moves the maintenance of one or more nonclustered indexes out of the Table Merge operator into their own separate operators, so that it can use a Split operator to convert these in place updates to equivalent insert and delete operations, and optionally combine them with a Collapse operator for better performance. However, at the time of writing I have not seen examples of this.
The optimizer ensures that foreign key and check constraints are checked by other operators in the execution plan. An Assert operator is typically used to force a rollback of the operation if a constraint would be violated.
When the action column indicates that a row needs to be deleted, the Table Merge operator finds the row in the heap by using the file, page and row number in the “row identifier” (RID) that is passed in in one of the input columns, typically named Bmknnnn where nnnn is a four-digit number that is unique within the execution plan. It then deletes this row.
For each additional nonclustered index specified in the Object property, the operator uses the values in its key columns in the original row, as retrieved from the existing heap entry, to find and then delete the existing index row.
The Predicate property is ignored for rows that have to be deleted. No constraints are checked by the operator; the optimizer ensures that foreign key and check constraints are checked by other operators in the execution plan. An Assert operator is typically used to force a rollback of the operation if a constraint would be violated.
Like any operator, Table Merge is invoked by calling its GetNext() method. And like any other operator, it responds by returning a row, with the columns listed in the Output List property. These columns can be values from the input data, new values of the columns in the clustered index, or old (before update) values of those columns. Unlike with other data modification operators, it seems based on observation that the Table Merge operator does not assign the column name with “_OLD” appended to columns for “before modification” data, but the generic Exprnnnn where nnnn is a unique number within the execution plan
The 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.
If the parent operator does not need any data, the Output List property is empty and an empty row is returned for each row deleted.
The properties below are specific to the Table Merge, 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 Table Merge operator are marked with a *.
|ActionColumn||This property lists the name of the column from the input that determines for each input row whether it updates an existing row, inserts a new row in the heap, or deletes an existing row from the heap.|
|DMLRequestSort||When set to true, the modification 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 Merge operator will modify, 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||When this property is present and set to true, the target of the Table Merge is a partitioned table.|
|Predicate||For the Table Merge operator, this property has two subproperties, simply called 1 and 2. Each of these has two subproperties of its own:
This table below lists the behavior of the implicit properties for the Table Merge operator.
|Batch Mode enabled||The Table Merge operator supports row mode execution only.|
|Blocking||The Table Merge operator is non-blocking.|
|Memory requirement||The Table Merge operator does not have any special memory requirement.|
|Order-preserving||The Table Merge operator is fully order-preserving.|
|Parallelism aware||The Table Merge 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 Merge operator is not segment aware.|