2011-11-21SQL Server Scripts for “Sorting and Grouping”

This section contains the code and execution plans for Chapter 6 in a SQL Server database.

Indexed Order By

DROP INDEX sales_date ON sales;
GO

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id);
GO

EXEC sp_updatestats;
GO

SET STATISTICS PROFILE ON;

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 ORDER BY  sale_date, product_id;

The execution does not perform a sort operation:

Nested Loops(Inner Join, OUTER REFERENCES:[Bmk1000])
 |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |  SEEK:[sales].[sale_date]=dateadd(day,(-1),getdate())
 |  ORDERED FORWARD)
 |--RID Lookup(OBJECT:([sales]),
    SEEK:[Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD

SQL Server uses the same execution plan, when sorting by PRODUCT_ID only.

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 ORDER BY product_id;

Using an greater or equals condition requires an Sort operation:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY product_id;

Although the row estimate got lower, causing the cost also to be lower:

Sort(ORDER BY:([test].[dbo].[sales].[product_id] ASC))
 |--Nested Loops(Inner Join, OPTIMIZED WITH UNORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1009]=BmkToPage([Bmk1000])))
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1007]
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Indexing ASC, DESC and NULLS FIRST/LAST

Scanning an index backwards:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date DESC, product_id DESC;

Nested Loops(Inner Join, OUTER WITH ORDERED PREFETCH)
 |--Nested Loops(Inner Join)
 |  |--Compute Scalar([...])
 |  |  |--Constant Scan
 |  |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |     SEEK:([sales].[sale_date] > [Expr1007]
 |      AND  [sales].[sale_date] < NULL) ORDERED BACKWARD)
 |--RID Lookup(OBJECT:([sales]),
     SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Mixing ASC and DESC causes an explicit sort:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC;

Sort(ORDER BY:([sale_date] ASC, [product_id] DESC))
 |--Nested Loops(Inner Join, OPTIMIZED WITH UNORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1009]=BmkToPage([Bmk1000])))
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1007] 
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([test].[dbo].[sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Ordering the index with mixed ASC/DESC modifiers:

DROP INDEX sales_dt_pr ON sales;
GO

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC);
GO

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC;

Nested Loops(Inner Join, WITH ORDERED PREFETCH)
 |--Nested Loops(Inner Join)
 |  |--Compute Scalar([...])
 |  |  |--Constant Scan
 |  |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |     SEEK:([sales].[sale_date] > [Expr1007]
 |      AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
 |--RID Lookup(OBJECT:([test].[dbo].[sales]),
    SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

SQL Server 2008R2 does not implement the order by NULLS extension.

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC NULLS LAST;

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NULLS'.

Indexed Group By

Pipelined group by execution:

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 GROUP BY product_id;

Stream Aggregate(GROUP BY:[sales].[product_id] )
 |--Nested Loops(Inner Join)
    |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |  SEEK:([sales].[sale_date]=dateadd(day,(-1),getdate()))
    |  ORDERED BACKWARD)
    |--RID Lookup(OBJECT:([sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Explicit Sort/Group when retrieving the stats for two days (parallelism disable for plan readability):

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 GROUP BY product_id
OPTION (MAXDOP 1);

Stream Aggregate(GROUP BY:[sales].[product_id])
 |--Sort(ORDER BY:([sales].[product_id] ASC))
    |--Nested Loops(Inner Join)
    |--Compute Scalar(DEFINE:[Expr1011]=BmkToPage[Bmk1000])
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1009]
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([test].[dbo].[sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

The Hash-Algorithm is used when aggregating a larger set:

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date >= DATEADD(day, -100, GETDATE())
 GROUP BY product_id
OPTION (MAXDOP 1);

Hash Match(Aggregate, HASH:([sales].[product_id]),
 |  RESIDUAL:([sales].[product_id] = [sales].[product_id])
 |--Table Scan(OBJECT:([sales]),
    WHERE:[sales].[sale_date]>=dateadd(day,(-100),getdate()))