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
(current versions)

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.

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, 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 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 inserted pseudo-table.
Table CardinalityThis 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 nameDescription
Batch Mode enabledThe Inserted Scan operator supports row mode execution only.
BlockingThe Inserted Scan operator is non-blocking.
Memory requirementThe Inserted Scan operator does not have any special memory requirement.
Order-preservingThe 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 awareThe 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 awareThe Inserted Scan operator is not segment aware.

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