Table Delete
Table Delete
Introduction
The Table Delete is used to delete rows from a heap table, and optionally (especially in a narrow update plan) from one or more nonclustered indexes as well; or to delete rows from a memory-optimized table and all its nonclustered indexes. The rows to be deleted are provided by the operator’s child subtree, and have to include a special column, called Bmknnnn (where nnnn is a 4-digit number generated by the optimizer and unique within the execution plan) to identify the row to be deleted. When deleting a row, the operator can return data from both that row as well as its input data (if any) to its parent.
Visual appearance in execution plans
Depending on the tool being used, a Table Delete 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 Delete 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 row / Read next row
The Table Delete operator reads rows from its child operator to identify the rows to be deleted. This identification is passed in the form of a special column, named Bmknnnn (where nnnn is a 4-digit number generated by the optimizer and unique within the execution plan).
For rowstore heap tables (Storage subproperty of the Object property is RowStore), the Bmknnnn column contains the RID, short for row identifier, consisting the page number, file number, and slot number of the row (see also RID Lookup).
For memory-optimized tables (Storage subproperty of the Object property is MemoryOptimized), the exact contents and internal structure of the Bmknnnn column is currently unknown.
Acquire locks
Before deleting data, the Table Delete 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, but it is never possible to completely eliminate all locking on data modifications, except in the case of memory-optimized tables that are designed to support fully lock-free 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.
Delete row
For a rowstore table, the Table Delete operator uses the Bmknnnn value from the input row to locate the row to delete in the heap. It can then, if needed, use additional data from that row to locate the corresponding index entries in additional nonclustered indexes (if any) in the Object property. Note that the nonclustered index entries are deleted immediately, but the heap entry is only locked and not deleted; the deletion of the row in the heap is completed upon the next call to the operator. This is checked in the GetNext() logic (as indicated in the flowchart), but also when the Close() method is called, to ensure complete deletion of the last row even if the operator is closed before the end of the input data is reached. Note that this is similar to how rows are deleted by the Clustered Index Delete operator.
For a memory-optimized table, the Table Delete operator probably uses the Bmknnnn value to directly locate the row to be deleted in memory. The deletion is done by changing the valid-to timestamp on the row; no data is actually deleted at this time. (The data will be deleted later when background processes determine that the row is no longer relevant for any still active transaction). This single act of marking the valid-to timestamp is sufficient to effectively logically delete the row from all indexes on the table, so the other (nonclustered) indexes in the Object property can be regarded as informational only; no action is needed on these individual indexes.
Heap & more objects?
The Object property of the Table Delete can hold one or more values. The first is always the table name, which either represents the rowstore heap, or the memory-optimized table. If there are more, then the rest are all nonclustered indexes on the same table, and rows deleted from the clustered index are “simultaneously” deleted 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 Delete operator can fetch these from the data in the clustered index when it reads the row to be deleted.
For a memory-optimized table, the nonclustered indexes in the Object property can be considered as informational only, since no specific action is needed to remove the data from these indexes; once the row is marked as deleted it is considered deleted in all indexes.
Return row
Like any operator, Table Delete 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. Most of the time these will be values from the input data, though any other columns from the table might be added to the Output List as well.
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. As mentioned before, when the parent operator processes this row, the data is locked but still present in the clustered index, but already actually deleted from any nonclustered indexes in the Object property.
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 Delete 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 Delete operator; they are repeated here for ease of use).
| Property name | Description |
|---|---|
| DMLRequestSort | When set to true, the delete 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 Delete operator will delete rows from, 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 Delete is a partitioned table. |
| WithUnorderedPrefetch | As far as currently known, prefetching is never used (and hence this property never included) for a Table Delete operator. |
Implicit properties
This table below lists the behavior of the implicit properties for the Table Delete operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Table Delete operator supports row mode execution only. |
| Blocking | The Table Delete operator is non-blocking. |
| Memory requirement | The Table Delete operator does not have any special memory requirement. |
| Order-preserving | The Table Delete operator is fully order-preserving. |
| Parallelism aware | The Table Delete 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 Delete operator is not segment aware. |