Physical Operator in execution plan in SQL server

 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.