Denali: Improved T-SQL Query Optimization

Part of the value in the ongoing evolution of the T-SQL language is that we are moving further and further towards being declarative rather than prescriptive ie: we are able to tell SQL Server what we want, rather than how to do it. Over time, that raises more and more possibilities for the optimizer to work with us to achieve a better outcome.

For example, note the following query against the AdventureWorksDW database:

SELECT rs.ProductKey, rs.OrderDateKey, rs.SalesOrderNumber,
       rs.OrderDateKey - (SELECT TOP(1) prev.OrderDateKey
                          FROM dbo.FactResellerSales AS prev
                          WHERE rs.ProductKey = prev.ProductKey
                          AND prev.OrderDateKey <= rs.OrderDateKey
                          AND prev.SalesOrderNumber < rs.SalesOrderNumber
                          ORDER BY prev.OrderDateKey DESC,
                                  prev.SalesOrderNumber DESC)
                AS DaysSincePrevOrder
FROM dbo.FactResellerSales AS rs
ORDER BY rs.ProductKey, rs.OrderDateKey, rs.SalesOrderNumber;

In this query, I’m trying to include details of how long it was since the previous order, beside the details of the current order. Note the option that the LAG operator now provides:

SELECT ProductKey, OrderDateKey, SalesOrderNumber,
       OrderDateKey - LAG(OrderDateKey)
                         OVER (PARTITION BY ProductKey
                                ORDER BY OrderDateKey, SalesOrderNumber)
                AS DaysSincePrevOrder
FROM dbo.FactResellerSales AS rs
ORDER BY ProductKey, OrderDateKey, SalesOrderNumber;