Physical Operator in an execution plan represents the actual method SQL Server uses to execute a specific step in a query. These operators show how SQL Server physically retrieves, processes, and manipulates data to satisfy the query request. Physical operators vary based on the nature of the query, the structure of the tables, and available indexes. They can be actions like scans, seeks, joins, sorts, and aggregations, among others.
Types of Physical Operators in
SQL Server
SQL Server uses various physical
operators based on the query requirements. Here are some of the most common
types:
Ø Index and Table Operators:
o
Clustered Index Scan: Scans an entire
clustered index.
o
Clustered Index Seek: Searches for
specific rows in a clustered index.
o
Non-Clustered Index Scan: Scans an entire
non-clustered index.
o
Non-Clustered Index Seek: Searches for
specific rows in a non-clustered index.
o
Table Scan: Reads all rows from a table
without an index.
Ø Join Operators:
o
Nested Loops Join: Combines two tables by
looping through each row of the outer table and finding matching rows in the
inner table.
o
Merge Join: Joins two sorted inputs by
merging them, which is efficient for large, sorted datasets.
o
Hash Match (Hash Join): Joins two inputs
by creating a hash table from one input and then matching it to rows in the
second input.
Ø Sorting and Aggregation Operators:
o
Sort: Sorts rows based on specified
columns.
o
Stream Aggregate: Computes aggregate
values, such as SUM, COUNT, and AVG, over a sorted set.
o
Hash Aggregate: Computes aggregate values
over unsorted sets by hashing values.
Ø Spool Operators:
o
Table Spool: Temporarily stores rows to
reuse them later in the execution.
o
Window Spool: Stores rows for windowed
operations.
o
Lazy Spool: Reads and stores rows only as
needed.
Ø Update, Insert, and Delete Operators:
o
Clustered Index Update: Updates rows in a
clustered index.
o
Non-Clustered Index Update: Updates rows
in a non-clustered index.
o
Table Delete: Deletes rows from a table.
o
Table Insert: Inserts rows into a table.
Ø Miscellaneous Operators:
o
Filter: Applies a filter to rows,
typically as a result of a WHERE clause.
o
Compute Scalar: Performs calculations on
a single row (e.g., +, -, *, /).
o
Sequence: Ensures sequential processing
of operations.
Ø Parallelism Operators: Split data to allow parallel processing, like Distribute Streams, Repartition Streams, and Gather Streams.
