Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators.
This is the table of descriptions to be used in the properties dialog showing the operator details in the execution plan graphical view.
Graphical execution plan icon
The Aggregate operator is a physical or logical operator that computes a new value using the SQL Server functions MIN, MAX, SUM, COUNT or AVG.
The Arithmetic Expression operator computes a new value from existing values in a row. Arithmetic Expression is not used in SQL Server 2014.
The Assert operator verifies a condition. The Assert operator is a physical operator.
The Assign operator assigns a value to a variable
The Asnyc Concat operator is used in remote distributed queries to get output rows from remote child nodes to send to the parent node.
The Bitmap operator is used to apply filtering to parallel query plans. Bitmap is a physical operator.
The Bitmap Create operator is a logical operator that shows where bitmaps are built.
The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. Bookmark Lookup is not used in SQL Server 2014. The Key Lookup operator also provides this functionality.
The Branch Repartition operator is a logical operator showing where iterators could be executed by parallel threads.
The Broadcast operator sends the set of input rows to multiple consumers.
The Build Hash operator indicates that a batch has table has been built.
The Cache operator is a logical operator that caches a single row of data. Cache is not used in SQL Server 2014.
Clustered Index Delete
The Clustered Index Delete operator deletes rows from a clustered index. Clustered Index Delete is a physical operator.
Clustered Index Insert
The Clustered Index Insert operator inserts rows into a clustered index. Clustered Index Insert is a physical operator.
Clustered Index Merge
The Clustered Index Merge operator merges a data stream and a clustered index. Clustered Index Merge is a physical operator.
Clustered Index Scan
The Clustered Index Scan operator scans a clustered index. It returns only rows matching the WHERE clause and sorts the results based on the ORDERED clause.
Clustered Index Seek
The Clustered Index Seek operator retrieves rows from a clustered index using the seeking ability of indexes.
Clustered Index Update
The Clustered Index Update operator updates rows in the clustered index. It only updates rows matching the WHERE clause.
The Collapse operator merges separate operations into a single more efficient operation.
Columnstore Index Scan
The Columnstore Index Scan operator scans the columnstore index specified in the query execution plan argument.
The Compute Scalar operator returns a computer scalar value from an evaluated expression.
The Concatenation operator returns rows scanned from multiple inputs. This is used for statements like UNION ALL.
The Constant Scan operator adds rows into a query.
The Convert operator converts one scalar type to another.
The Cross Join operator is a logical operator that joins each row from input with each row from another input.
The catchall operator is a placeholder icon when an operator does not match any other query operators.
The Cursor operator describes the execution of a query or update that use cursor operations.
The Declare operator allocates a local variable.
The Delete operator deletes rows from an object.
The Deleted Scan operator scans the deleted table within a trigger.
The Distinct operator removes duplicate rows.
The Distinct Sort operator removes duplicate rows and sorts the resulting set of rows.
The Distribute Streams operator is used to break records from a single input to multiple output streams in a parallel query plan.
The Dynamic operator uses a cursor to see all changes made by others.
The Eager Spool operator stores each row in the input to tempdb so that this cached data can be used if the operator is rewound.
The Fetch Query operator returns rows from a fetch issued against a cursor.
The Filter operator returns only rows that match a filter expression.
The Flow Distinct operator removes duplicate rows but returns each row as it is processed.
Full Outer Join
The Full Outer Join operator is a logical operator that implements an OUTER JOIN. Results in all matching rows from two streams plus rows for each row in the streams that did not have matches.
The Gather Streams operator is used to consume multiple input streams and combine them into a single output stream in parallel query plans.
The Has Match operator creates a hash table from the rows in the build input.
The Inned Join operator returns rows that satisfy the JOIN of the first (top) input with the second (bottom) input.
The Insert logical operator inserts input rows into the object specified in the Argument column.
The Inserted Scan operator scans the inserted table.
The Intrinsic operator runs an internal Transact-SQL function.
The Iterator operator is a placeholder icon used when no match is found for the iterator operation.
The KEY LOOKUP operator signals a lookup using a bookmark on a table with a clustered index.
The KEYSET operator uses a cursor that can only see updates and not inserts.
The LANGUAGE ELEMENT operator is a placeholder icon when no matching language constructs can be found.
The LAZY SPOOL operator stores each row in the input to tempdb so that this cached data can be used if the operator is rewound. The rows are only copied to tempdb as they are processed.
Left Anti Semi Join
The LEFT ANTI SEMI JOIN operator returns rows from the first input where there is no match in the second input.
Left Outer Join
The LEFT OUTER JOIN operator returns rows from the first input that satisfy the join with the second input.
Left Semi Join
The LEFT SEMI JOIN operator returns rows from the first input that have a match with the second input.
Log Row Scan
The LOG ROW SCAN operator scans the transaction log.
The MERGE INTERVAL operator merges multiple intervals to create a minimal set of non-overlapping intervals.
The MERGE JOIN operator performs one of the many possible join operations.
The NESTED LOOPS operator perform the logical operations to satisfy many join operations that need a loop to search the inner table for rows in the outer table.
Nonclustered Index Delete
The NONCLUSTERED INDEX DELETE operator deleted rows from a non-clustered index.
The INDEX INSERT operator inserts rows into a non-clustered index.
The INDEX SCAN operator scans a non-clustered index. It returns only rows matching the WHERE clause
The INDEX SEEK operator retrieves rows from a non-clustered index using the seeking ability of indexes.
The INDEX SPOOL operator copies input rows to tempdb and builds a non-clustered index for these rows.
Nonclustered Index Update
The NONCLUSTERED INDEX UPDATE operator updates rows from its input in the non-clustered index.
Online Index Insert
The ONLINE INDEX INSERT operator is a physical operator indicating that an index create, alter or drop is performed online.
The PARALLELISM operator the logical operations of distribute, gather or repartition streams.
Parameter Table Scan
The PARAMETER TABLE SCAN operator scans a table that acts as a parameter of the query.
The PARTIAL AGGREGATE operator is a logical operator that aggregates input rows to prevent writing to disk in parallel plans.
The POPULATION QUERY operator populates a cursor's work table.
The REFRESH QUERY operator fetches current data for rows.
The REMOTE DELETE operator deletes rows from an remote object.
Remote Index Scan
The REMOTE INDEX SCAN operator scans a remote index.
Remote Index Seek
The REMOTE INDEX SEEK operator retrieves rows using a remote index.
The REMOTE INSERT operator inserts rows into a remote object.
The REMOTE QUERY operator submits a query to a remote source.
The REMOTE SCAN operator scans a remote object.
The REMOTE UPDATE operator updates a remote object.
The REPARTITION STREAMS operator creates multiple out streams from multiple input streams while applying a bitmap filter.
The RESULT operator is the query plan return data.
The RID LOOKUP operator is a bookmark lookup on a heap.
Right Anti Semi Join
The RIGHT ANTI SEMI JOIN operator returns rows from the second input where the is no match in the first input.
Right Outer Join
The RIGHT OUTER JOIN operator returns rows from the second input that satisfy the join with the first input.
Right Semi Join
The RIGHT SEMI JOIN operator returns rows from the second input that have a match with the first input.
Row Count Spool
The ROW COUNT SPOOL operator returns empty rows for each row in the input stream.
The SEGMENT operator uses the value of columns to divide the input set into segments.
The SEGMENT REPARTITION operator marks the boundaries of regions whose iterators can be run in parallel threads.
The SEQUENCE operator executes each input in sequence.
The SEQUENCE PROJECT operator adds columns to the input set, divides the input set into segments and outputs one segment at a time.
The SNAPSHOT operator creates a cursor that cant see changes by others.
The SORT operator sorts incoming rows.
The SPLIT operator creates a delete and insert operation out of each update operation.
The SPOOL operator saves an intermediate query to tempdb.
The STREAM AGGREGATE operator groups rows by columns and calculates aggregate expressions.
The SWITCH operator copies the appropriate input stream to the output stream by evaluating an expression.
The TABLE DELETE operator deletes rows from a table.
The TABLE INSERT operator inserts rows into a table.
The TABLE MERGE operator applies a merge data stream to a heap.
The TABLE SCAN operator retrieves rows from a table.
The TABLE SPOOL operator scans the input and places the rows into tempdb.
The TABLE UPDATE operator updates rows in a table.
The TABLE-VALUED FUNCTION operator evaluates a table-valued function and stores the resulting rows in tempdb.
The TOP operator returns only the specified number of rows from the input.
Top N Sort
The TOP N SORT operator returns only the specified number of rows from the input and sorts them.
The UDX operator implements XQuery and XPath operations.
The UNION operator combines multiple inputs and removes duplicates.
The UPDATE operator updates a specified object from the rows in its input.
The WHILE operator represents a SQL while loop.
The WINDOW SPOOL operator expands the input rows into sets of rows that represent the window associated with the row.
SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >