Table Merge

Introduction

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. Since SQL Server 2016 SP2-CU5, SQL Server 2017 RTM-CU13, and SQL Server 2019, the optimizer no longer generates plans that specify additional nonclustered indexes for a Table Merge.

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:

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

(Yes, the icon for the current versions of the Microsoft tools is exactly the same as the legacy SSMS icon)

Algorithm

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.

Acquire locks

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.

Action Column

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.

Insert row

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.

Update row

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.

Delete row

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.

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.

More objects?

In older versions of SQL Server (see above for the exact list), the Object property of the Table Merge could hold one or more values. The first was always a heap table. If there were more, then the rest were all nonclustered indexes on the same table, and rows modified in the heap table were “simultaneously” modified in those nonclustered indexes as well. This is typical for a narrow update plan.

As explained above, the key values of for the extra indexes in the Object property do not need to be in the input. The Table Merge operator can fetch these from the data in the clustered index when it reads the row to be modified.

The option to update multiple nonclustered indexes in addition to the heap table in the same Table Merge was presumably removed in order to fix a bug. It is unknown whether this bug fix only prevents the optimizer from producing such plans, or whether the functionality to process multiple objects has actually been removed from the operator’s code.

Return row

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.

Operator properties

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 *.

Property name	Description
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). On older versions of SQL Server (see main text), 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: Index Kind: Represents the kind of object to be modified. For a Table Merge, the Index Kind is Heap on the first Object. If extra nonclustered indexes are listed, they will have Index Kind set to NonClustered. Storage: Determines the storage type of the object. For a Table Merge, this is always RowStore for all Objects.
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: ScalarOperator: This lists either all columns and their values if the SetPredicateType is Insert, or the columns to be updated and their new values if SetPredicateType is Update. SetPredicateType: This is set to either Insert or Update, to indicate whether the ScalarOperator specification should be used for rows that are an insert or an update (as specified by the column listed in the Action Column property).

Implicit properties

This table below lists the behavior of the implicit properties for the Table Merge operator.

Property name	Description
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.