by Markus Winand.

SQL Server Scripts for “Sorting and Grouping”


This section contains the code and execution plans for Chapter 6, “Sorting and Grouping 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()))

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

…to answer your current SQL questions.

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license