Logical Operators in an execution plan represent the high-level, logical steps SQL Server takes to satisfy a query. They define what SQL Server needs to do (like a join, filter, or aggregation), while the Physical Operators define how SQL Server actually performs those actions. Logical operators are abstract descriptions of operations, helping understand SQL Server’s approach to processing data.
Types of Logical Operators in SQL Server
Ø Join Operators: Used when SQL Server needs to combine rows from two tables based on specific conditions:
o
Inner
Join: Returns only the rows where there is a match in both tables.
o
Left
Outer Join: Returns all rows from the left table and matched rows from the
right table; if there’s no match, NULL is returned from the right table.
o
Right
Outer Join: Returns all rows from the right table and matched rows from the
left; NULL is returned from the left if no match is found.
o
Full
Outer Join: Returns all rows when there’s a match in either table; fills in
NULL values when no match exists in one table.
o
Cross
Join: Combines all rows from two tables (Cartesian product).
Ø Set Operators :Used for combining results from multiple queries or tables:
o
Union:
Combines results from two or more queries and removes duplicates.
o
Union All:
Combines results from two or more queries without removing duplicates.
o
Intersect:
Returns only the rows common to both queries.
o
Except:
Returns rows from the first query that aren’t in the second query.
Ø Logical Scan Operators: Describes how SQL Server logically accesses data:
o Table Scan: Reads
all rows from a table without filtering.
o Index Scan: Reads
all rows from an index, typically used when most rows satisfy the query
criteria.
Ø Logical
Seek Operators : Used when SQL Server accesses
specific rows in an index:
o Index Seek: Finds
specific rows using an index key, which is faster than scanning the entire
table.
o Range Seek: Accesses
rows within a specific range of index values.
Ø Filter
Operator: Applies
criteria from the WHERE
or HAVING clause to
limit rows returned by a query. This logical operator identifies rows that meet
specified conditions.
Ø Aggregation
Operators : Used to calculate summary values such as sums, averages, counts,
etc.:
o Stream
Aggregate: Computes aggregates on sorted input (e.g., SUM, COUNT, AVG).
o Hash
Aggregate: Computes aggregates on unsorted input by creating hash tables
for each group.
Ø Compute
Scalar Operator: Calculates expressions for each
row, like performing arithmetic operations, casting data types, or computing
scalar functions in a query.
Ø Logical
Sort Operator: Sorts rows based on one or more columns as specified by an ORDER BY clause. Sorting can also be required for
certain operations, such as Merge
Join or Stream
Aggregate.
Ø Concatenation
Operator: Combines multiple sets of rows from different sources. Often seen
in UNION ALL queries or
queries where data from multiple tables needs to be combined without removing
duplicates.
Ø Spool Operators : Temporarily stores rows to reuse them in the same query execution:
o Index Spool: Similar
to a table spool but works with indexes.
o Lazy Spool: Reads
rows only when needed, reducing memory usage.
o Table Spool: Stores a
subset of rows for reuse, which can improve performance in certain situations.
Ø Parallelism
Operators : Distributes data across multiple processors to execute parts of a
query in parallel, improving performance on large datasets:
o Distribute
Streams: Divides data into separate streams for parallel processing.
o Repartition
Streams: Reorganizes data between processors based on a new distribution
key.
o Gather
Streams: Combines results from multiple streams into a single output.
Ø Segment
and Sequence Operators
o Segment: Defines
data boundaries for further operations, useful in OVER clause
operations.
o Sequence: Ensures
sequential execution of operations in a query, often used in queries with
ordered results or multiple modifications.
