Inserted Scan
Introduction
The Inserted Scan operator is only found in execution plans for code in triggers. It is used for queries that read data from the inserted pseudo-table. Its counterpart, Deleted Scan, reads from the deleted pseudo-table.
This pseudo-table contains a copy of all the rows that were inserted in AFTER INSERT triggers, or the new content of the data in all affected rows in AFTER UPDATE triggers. In INSTEAD OF INSERT or INSTEAD OF UPDATE triggers, the data in the inserted pseudo-table is the data that would have been inserted, or the data as it would have been after the update. In AFTER DELETE and INSTEAD OF DELETE triggers, using the inserted pseudo-table is allowed but returns no data.
On a conceptual level, an Inserted Scan operator behaves the same as Table Scan. It simply reads and returns the rows in the inserted pseudo-table in whatever order they are stored.
Visual appearance in execution plans
Depending on the tool being used, a, Inserted Scan 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 Inserted 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 Inserted Scan operator come from the inserted pseudo-table, one of two temporary, memory-resident tables that are maintained internally by operators that process data modifications. The other pseudo-table is deleted. 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.
| Modification | AFTER trigger | INSTEAD OF trigger |
|---|---|---|
| Insert | No 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. |
| Update | Add 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. |
| Delete | Add 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, Inserted Scan does not support predicate pushdown. If not all data from the inserted pseudo-table is required, the Inserted 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 Inserted 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 Inserted Scan operator are marked with a *.
| Property name | Description |
|---|---|
| Object | This property shows the name of the trigger’s parent table. |
| Partitioned | This 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 inserted pseudo-table. |
| Table Cardinality | This property shows the number of rows in the inserted pseudo-table when the plan was last compiled. |
Implicit properties
This table below lists the behavior of the implicit properties for the Inserted Scan operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Inserted Scan operator supports row mode execution only. |
| Blocking | The Inserted Scan operator is non-blocking. |
| Memory requirement | The Inserted Scan operator does not have any special memory requirement. |
| Order-preserving | The Inserted Scan operator returns rows from the inserted 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 Inserted Scan to be unordered. |
| Parallelism aware | The Inserted 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 aware | The Inserted Scan operator is not segment aware. |