Deleted Scan

Introduction

The Deleted Scan operator is only found in execution plans for code in triggers. It is used for queries that read data from the deleted pseudo-table. Its counterpart, Inserted Scan, reads from the inserted pseudo-table.

This pseudo-table contains a copy of all rows that has just been deleted in AFTER DELETE triggers, or the original data of all affected rows in AFTER UPDATE triggers. In INSTEAD OF DELETE or INSTEAD OF UPDATE triggers, the data in the deleted pseudo-table is the current data in the rows that would have been deleted or updated. In AFTER INSERT and INSTEAD OF INSERT triggers, using the deleted pseudo-table is allowed but returns no data.

On a conceptual level, a Deleted Scan operator behaves the same as Table Scan. It simply reads and returns the rows in the deleted pseudo-table in whatever order they are stored.

Visual appearance in execution plans

Depending on the tool being used, a Deleted Scan operator is displayed in a graphical execution plan as shown below:

SQL Server Management Studio

Azure Data Studio

Plan Explorer

(version 17.4 and up)

(until version 17.3)

(no icon)

Algorithm

The basic algorithm for the Deleted Scan 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.

The inserted and deleted pseudo-tables

The rows read and returned by the Deleted Scan operator come from the deleted pseudo-table, one of two temporary, memory-resident tables that are maintained internally by operators that process data modifications. The other pseudo-table is inserted. Both these pseudo-tables have the same columns and data types as the parent table of the trigger, but no indexes. The exact internal storage structures used for these pseudo-tables is unknown.

How these two pseudo-tables are maintained depends on the type of trigger (AFTER or INSTEAD OF), and the type of modification (insert, update, delete), as shown below. Note that there is no row for the merge operation, since SQL Server processes MERGE statements by translating them to the equivalent set of insert, update, and delete operations.

ModificationAFTER triggerINSTEAD OF trigger
InsertNo action for deleted.
Add a copy of the row to inserted.
No action for deleted.
Add a row to inserted with the data of the new row.
UpdateAdd a copy of the row before the update to deleted.
Add a copy of the row after the update to inserted.
Add a copy of the row to deleted.
Add a copy of the row as it would be after the update to inserted.
DeleteAdd a copy of the row to deleted.
No action for inserted.
Add a copy of the row to deleted.
No action for inserted.

Predicate pushdown

Unlike most other scan operators, Deleted Scan does not support predicate pushdown. If not all data from the deleted pseudo-table is required, the Deleted Scan will still return all rows, and unwanted rows will be removed by an explicit Filter operator.

Operator properties

The properties below are specific to the Deleted Scan 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 Deleted Scan operator are marked with a *.

Property nameDescription
ObjectThis property shows the name of the trigger’s parent table.
PartitionedThis property is present and set to True when the trigger’s parent table is a partitioned table. As far as known, this does not affect the structure of the deleted pseudo-table.
Table CardinalityThis property shows the number of rows in the deleted pseudo-table when the plan was last compiled.

Implicit properties

This table below lists the behavior of the implicit properties for the Deleted Scan operator.

Property nameDescription
Batch Mode enabledThe Deleted Scan operator supports row mode execution only.
BlockingThe Deleted Scan operator is non-blocking.
Memory requirementThe Deleted Scan operator does not have any special memory requirement.
Order-preservingThe Deleted Scan operator returns rows from the deleted pseudo-table in whatever order they happen to be stored. Since the storage structure is unknown, it is impossible to predict the order.
For purposes of ordering in the execution plan, the optimizer considers the output of the Deleted Scan to be unordered.
Parallelism awareThe Deleted Scan operator does not support parallelism. It can only be used in a serial plan, or in a serial section of a parallel plan.
Segment awareThe Deleted Scan operator is not segment aware.
Menu

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close