I'm going to address this part of the question:
...this is the execution plan can anyone explain the steps more?
Assuming the table definition looks something like this:
CREATE TABLE dbo.Employee
[E-Number] integer NOT NULL,
[E-Name] nvarchar(50) NOT NULL,
Department nvarchar(30) NOT NULL,
Salary smallmoney NOT NULL
The following query (not my recommendation, just an example):
FROM dbo.Employee AS E1
FROM dbo.Employee AS E2
E2.Salary > E1.Salary
Gives this execution plan (with no data in the table):
The numbers correspond to the Node ID property of each operator.
E1 Table Scan (4) returns all rows (in no particular order). For each row:
- The Nested Loops join (3) executes the inner side, passing the current
E1.Salary value as a parameter.
E2 Table Scan (8) finds rows matching
E2.Salary > E1.Salary
- The Distinct Sort (7) groups rows by
- The Stream Aggregate (6) counts the number of rows (1 per distinct
E2.Salary). The new count column is given the label
- The Compute Scalar (5) converts the native count data type (
integer because the query specifies
COUNT rather than
COUNT_BIG. The new column is given the label
- As rows emerge from the Nested Loops join, the Filter (2) applies the predicate
Expr1004 = 1
- The final Distinct Sort returns the distinct
With duplicate Salary values
If we add a few rows to the table, with some duplicate
([E-Number], [E-Name], Department, Salary)
(1, 'A', 'D1', $1),
(2, 'B', 'D1', $1),
(3, 'C', 'D1', $2),
(4, 'D', 'D1', $2),
(5, 'E', 'D1', $3),
(6, 'F', 'D1', $3);
The query above produces a plan with a couple of new operators (highlighted):
The new Sort orders rows by
E1.Salary. The ensures that any duplicate
Salary values are presented to the join sequentially.
The Lazy Index Spool saves the output from the operators below it as they arrive. These are the Stream Aggregate, Distinct Sort, and
E2 Table Scan (with predicate), which as described before filter
E2 (using the current value of
E1.Salary), remove duplicate
E2.Salary values, and count the resulting rows. The Lazy Index Spool saves recomputing the result in case the same
Salary value from table
E1 is presented on the next iteration of the Nested Loops join.
Because the Sort ordered rows from
E1.Salary, any duplicates are presented to the join one after the other, so the saved result in the spool can be reused instead of re-running the Stream Aggregate, Distinct Sort, and
E2 Table Scan each time.
The index on the spool is keyed by
Salary, making it quick to find the saved
COUNT(DISTINCT E2.Salary) result for the current
If you look at the Actual Executions value for each operator in the plan, you will see that the Lazy Index Spool executes 6 times (once per outer row), whereas the Stream Aggregate, Distinct Sort, and
E2 Table Scan execute only 3 times (once per distinct salary value).
The optimizer introduces the extra Sort and Spool as a performance optimization, based on automatically collected statistical information about the data in the table.