Table Update
Introduction
The Table Update operator is typically used to update rows in a heap table, but can also insert and delete rows in that same table. Optionally (especially in a narrow update plan) a Table Update can update one or more nonclustered indexes as well. The rows to be affected, as well as the required action and the new values of all columns in the table, are produced by the operator’s child subtree. After updating a row, the operator returns data from that row to its parent; this data can be both the original or the new data.
The Table Update operator is in most ways identical in functionality to the Index Update operator, except that it updates data stored in a heap table. One key difference, though, is that a Table Update 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 updated 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 Update in the execution plan.
Visual appearance in execution plans
Depending on the tool being used, a Table Update operator 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 Table Update 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.
Read first / next row
Unlike the other update operators, the Table Update operator always has a child operator that produces information for the required modifications. Rows to be modified are found by executing GetNext() on the child operator. The returned row will at least contain a special column named Bmknnnn (where nnnn is a four-digit number generated to be unique in the execution plan), that stores the file number, page number, and slot number of the row to be updated. This special internal column is used to identify the row in the heap table, read it (using the same logic as the RID Lookup operator) if needed to find key values of nonclustered indexes to modify and of columns in the Output List property, and then update it.
Acquire locks
Before modifying data, the Table Update 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 (if any). 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 Update operator may optionally have its Action Column property set. If so, then this lists the name of one of the columns in the input data that will be interpreted as specifying, 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 Clustered Index Update) are:
- 1: Update
- 3: Delete
- 4: Insert
It is currently unknown whether there are more possible values.
If the Action Column property is not provided, then all input rows will be processed as updates to an existing row.
As far as currently known, it is not possible for a Table Update operator to have both its Action Column property provided and also have additional nonclustered indexes listed in the Object property.
Insert row
When the action column indicates that a row needs to be inserted, the Table Update operator uses the Predicate property to find the values for all columns in that row. The Table Update 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 is absent or indicates that a row needs to be updated, the Table Update operator uses the value in the Bmknnnn column in the input to locate the row. It then sets the columns in that row to the values indicated in the Predicate property. If the row still fits on the page of its current (old) location, it is updated in place. If the size of the row has grown and it no longer fits on the current page, then it is stored on a new page, and the data in the original location is replaced with a forwarding pointer. If the old location was already at the end of a forwarding pointer from the original location, then this page slot is now unused again.
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 Update operator uses the value in the Bmknnnn column in the input to locate the row, and then deletes it from the heap table. If the row was a forwarded row, then both the current and the original location are deleted. 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?
The Object property of the Table Update operator can hold one or more values. The first is always a heap table. If there are more, then the rest are all nonclustered indexes on the same table, and rows modified in the table are “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 Update operator can fetch these from the data in the heap when it reads the row to be modified.
As far as currently known, the Object property can only hold more than one value if the Action Column property is not included.
Return row
Like any operator, Table Update 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. The latter can be recognized in the execution plan by their name: the same name as the column, with “_OLD” appended.
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 Update 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 Update operator are marked with a *.
| Property name | Description |
|---|---|
| ActionColumn | When this property is present, it lists the name of the column from the input that determines for each input row whether it updates index data in place, inserts new data in the index, or deletes an existing row from the index. When this property is missing, all input rows are considered to be an update in place. As far as currently known, this property can’t be included if the Object property includes more than one value. |
| DMLRequestSort | When set to true, the update 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 Update 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 Update is a partitioned index. |
| 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. For input rows that are to be processed as a delete (as determined by the data in the column listed in the Action Column property), this property is effectively ignored. |
| WithOrderedPrefetch | As far as currently known, prefetching is never used (and hence this property never included) for a Table Update operator. |
Implicit properties
This table below lists the behavior of the implicit properties for the Table Update operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Table Update operator supports row mode execution only. |
| Blocking | The Table Update operator is non-blocking. |
| Memory requirement | The Table Update operator does not have any special memory requirement. |
| Order-preserving | The Table Update operator is fully order-preserving. |
| Parallelism aware | The Table Update 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 Update operator is not segment aware. |