Showplan logical and physical operators

This topic provides the descriptions of the logical and physical operators.

Execution Plan Icon	Name	Operators and constructs	Description
	Assert	Physical operator	Verifies a condition during query execution. If the expression evaluates to NULL, the row passes through the operator and execution continues. If the expression evaluates to a NOT NULL value, an error is raised. Commonly used to enforce CHECK constraints and FOREIGN KEY relationships in INSERT, UPDATE, DELETE, and MERGE statements.
	Assign	Language element	Assigns a value to a variable.
	Bitmap	Physical operator	Applies bitmap filtering in parallel query plans to improve performance with large data sets. Filters out rows that cannot produce JOIN matches before passing them to downstream operators such as Parallelism.
	Clustered Index Delete	Physical operator	Deletes rows from the clustered index specified in the Argument column of the query execution plan. If the Argument column includes a WHERE predicate, only rows that satisfy the predicate are deleted.
	Clustered Index Insert	Physical operator	Inserts rows into the clustered index. The rows can come from the operator’s child subtree or be provided directly to the Insert operator.
	Clustered Index Merge	Physical operator	Applies a merged data stream to a clustered index. Depending on the runtime value of the ACTION column specified in the Argument column, it deletes, updates, or inserts rows into the clustered index.
	Clustered Index Scan	Logical and physical operator	Scans a clustered index specified in the Argument column of the execution plan and returns all rows or only those that satisfy a WHERE predicate, if defined.
	Clustered Index Seek	Logical and physical operator	Performs a seek operation on a clustered index using a specified predicate. Retrieves only the rows that match the SEEK condition.
	Clustered Index Update	Logical and physical operator	Updates rows in a clustered index as specified in the operator arguments.
	Collapse	Logical and physical operator	Optimizes update processing by collapsing a pair of DELETE and INSERT operations into a single UPDATE.
	Columnstore Index Scan	Physical operator	Scans all data from a columnstore index specified in the Argument column of the query execution plan.
	Compute Scalar	Logical and physical operator	Calculates a new scalar value based on input data. The result can be returned to the user or used in filters, joins, or other computations.
	Concatenation	Logical and physical operator	Combines multiple input sets into a single output. Often used to implement the Transact-SQL UNION ALL construct.
	Constant Scan	Logical and physical operator	Introduces a constant row into a query. It is often used to add columns to the row produced by a Constant Scan.
	Catchall	Placeholder icon	Indicates that the graphical showplan could not assign a specific icon to an element. Does not necessarily indicate an error. There are three types of catchall icons: blue (for iterators) orange (for cursors) green (for Transact-SQL language elements)
	Delete		Deletes rows from a target object. If a WHERE predicate is present, only matching rows are deleted.
	Deleted Scan	Physical operator	Appears only in execution plans for trigger code. Scans the deleted pseudo-table within the trigger.
	Dynamic	Physical operator	Represents a dynamic cursor that reflects all changes made to the underlying data as it is fetched.
	Fetch Query	Physical operator	Fetches rows as part of a FETCH operation on a cursor.
	Filter	Physical operator	Scans the input, returning only those rows that satisfy the condition.
	Hash Match	Physical operator	Builds a hash table from the first input and uses it for joins or aggregations on the second input.
	Insert	Logical operator	Inserts rows into a target object. Implemented physically using operators like Table Insert, Index Insert, or Clustered Index Insert.
	Inserted Scan	Logical and physical operator	Scans the inserted pseudo-table in triggers.
	Key Lookup	Physical operator	Retrieves a full row from a clustered index using a key from a non-clustered index. Often appears alongside a Nested Loops operator and may signal a need for a covering index.
	Keyset	Physical operator	Represents the declaration of a keyset-driven cursor that can detect updates made by others but not inserts.
	Log Row Scan	Logical and physical operator	Scans the transaction log.
	Merge Interval	Logical and physical operator	Combines multiple sets of potentially overlapping intervals into fewer, non-overlapping intervals, which are then used to seek index entries.
	Merge Join	Physical operator	Performs a join by merging two sorted inputs based on the join keys. Supports various join types, including inner, outer, semi, and anti joins.
	Nested Loops	Physical operator	Performs join operations such as inner join, left outer join, left semi join, and left anti semi join. For each row in the outer input, the operator scans the inner input to find matching rows.
	Nonclustered Index Delete	Physical operator	Deletes input rows from a non-clustered index.
	Index Insert	Physical operator	Inserts input rows into a nonclustered index.
	Index Scan	Logical and physical operator	Reads all data from a non-clustered index.
	Index Seek	Logical and physical operator	Uses the seeking ability of indexes to find a specific row in a nonclustered index based on a key value.
	Index Spool	Physical operator	Stores its input rows in an internal, indexed worktable in the tempdb database. This worktable exists only for the duration of the query and allows re-processing specific subsets of data without rescanning the input.
	Nonclustered Index Update	Physical operator	Updates rows in a nonclustered index.
	Online Index Insert	Physical operator	Indicates that an index creation, alteration, or drop operation is performed without taking the index offline.
	Parallelism	Physical operator	Manages the distribution of rows between threads in parallel query plans. Also referred to as the Exchange operator.
	Parameter Table Scan	Logical and physical operator	Scans a table used as a parameter in the current query. Common in stored procedures for INSERT operations.
	Population Query	Physical operator	Executes the query defined by an OPEN CURSOR statement and populates the result set.
	Refresh Query	Physical operator	Fetches current data for rows in the fetch buffer.
	Remote Delete	Logical and physical operator	Deletes rows from a remote object.
	Remote Index Scan	Logical and physical operator	Seeks specific rows in a remote index using key values.
	Remote Index Seek	Logical and physical operator	Uses the seeking ability of a remote index object to retrieve rows.
	Remote Insert	Logical and physical operator	Inserts rows into a remote object.
	Remote Query	Logical and physical operator	Sends a query to a remote data source and retrieves the result set.
	Remote Scan	Logical and physical operator	Reads all data from a remote object.
	Remote Update	Logical and physical operator	Updates rows in a remote object.
	RID Lookup	Physical operator	Retrieves a row from a heap table based on a RID value. Typically follows a nonclustered index seek.
	Row Count Spool	Physical operator	Scans the input, counts the number of rows in it, and then returns the same number of empty rows.
	Segment	Physical and logical operator	Divides the input into segments based on the value of one or more columns. Marks the start of each segment.
	Sequence	Logical and physical operator	Processes two or more inputs in sequence, returning only the rows from the final input.
	Sequence Project	Logical and physical operator	Adds new columns to perform calculations over ordered input. Divides input into segments and processes each segment.
	Snapshot	Physical operator	Represents the declaration of a static cursor that does not see changes made by others.
	Sort	Logical and physical operator	Sorts input rows based on specified columns and returns them in order.
	Split	Logical and physical operator	Optimizes update processing by replacing update operations with the corresponding DELETE and INSERT operations.
	Stream Aggregate	Physical operator	Calculates aggregation results by reading a sorted input stream and returning a single row for each set of rows with the same key value.
	Switch	Logical and physical operator	Executes only one of its inputs; which input is executed is determined at runtime.
	Table Delete	Physical operator	Deletes rows from a heap table and, optionally, associated nonclustered indexes.
	Table Insert	Physical operator	Inserts rows into a heap table and, optionally, into associated nonclustered indexes.
	Table Merge	Physical operator	Applies a stream of changes (insert, update, delete) to a heap table and associated indexes.
	Table Scan	Logical and physical operator	Reads all rows from a heap table. Returns all rows or only those matching a WHERE condition.
	Table Spool	Physical operator	Stores a copy of input rows in a hidden spool table in tempdb. Used to avoid rescanning the original input.
	Table Update	Physical operator	Updates rows in a heap table and, optionally, associated indexes.
	Table-valued Function	Language element	Executes a table-valued function and stores the results in a temporary table.
	Top	Logical and physical operator	Scans the input and returns only the first specified number or percentage of rows, optionally skipping an initial set and including additional rows in case of ties on specified columns.
	UDX	Logical and physical operator	Implements XQuery and XPath operations in SQL Server.
	Update	Logical operator	Updates rows in a target table. Backed by one of the physical operators: Table Update, Index Update, or Clustered Index Update.