Why I Love T-SQL Window Functions, and You Should Too!

By Kathi Kellenberger

I had been using ROW_NUMBER in my T-SQL code for years without realizing that it is one of a group of incredibly useful functions called window functions. Only when putting together a presentation on new T-SQL features in the SQL Server 2012 release did I realize that there was so much to talk about that I created a separate presentation just on the new window functions. Eventually that one presentation turned into two presentations, my book published by Apress, and a Pluralsight course. I have been telling anyone who will listen about these great functions, and I find myself using them on a daily basis at work.

Window functions allow you to perform calculations over a set of rows. They are similar in operation to grouping with aggregate functions, but they enable an important difference: Aggregate queries return one row per group, and the details are eliminated in the results. Queries involving window functions return all the detail rows along with the result of the window function expression. You have detail, and get the aggregate too.

When I speak at SQL Server and developer events about window functions, usually about half the room is familiar with ROW_NUMBER. A handful will know about more than that. That is disappointing since these useful functions have been available for years.

My favorite window function is called LAG. LAG allows you to grab any column from another row in the result set. You can accomplish the same thing with other methods, but the performance is much worse. Following are two queries against the AdventureWorks database. Both queries give the same result. The first does so through an outer join, whereas the second takes advantage of LAG.

_{--List the customers with order dates and the days since the prev order}

_{SELECT CustomerID, OrderDate,}

     _{DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder}

_{FROM Sales.SalesOrderHeader AS SOH}

_{OUTER APPLY (}

     _{SELECT TOP(1) OrderDate AS PrevOrder}

     _{FROM Sales.SalesOrderHeader AS OA}

     _{WHERE OA.CustomerID = SOH.CustomerID}

           _{AND OA.OrderDate < SOH.OrderDate}

     _{ORDER BY OrderDate) AS OAPPLY}

_{ORDER BY CustomerID, OrderDate;}

_{--Produce the same results using LAG}

_{SELECT CustomerID, OrderDate,}

     _{DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder}

_{FROM Sales.SalesOrderHeader}

_{ORDER BY CustomerID, OrderDate;}

The second query using the LAG function is simpler to write and performs better. The actual execution plan shows that the first query takes 99% of the resources in the batch. The second query takes just 1% of the resources. Here are the two execution plans, and you can see for yourself:

If you are not familiar with window function syntax, the LAG query may be a bit confusing. All window functions will have an OVER clause that defines the window, or set of rows, for the calculation. I don’t want to compare order dates across customers, so I used the PARTITION BY option to divide the rows into groups by CustomerID:

_{LAG(OrderDate) OVER(PARTITION BY CustomerID ...}

The OVER clause also has an ORDER BY option. This is required in the case of LAG, so that the rows will be lined up in the correct order for the engine to grab respective values from their correct rows. My final LAG invocation looks as follows:

_{LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)}

LAG is just one function of many window functions. These functions don’t actually let you do anything that is impossible without them, but they definitely make coding easier. You’ll also find that many times your queries run faster when formulated using these new functions.

I end up using window functions on a daily basis. They make my life easier and improve the performance of my queries. I love to use them, and you should too.

About the Author

​​​​​​​Kathi Kellenberger is a consultant with Linchpin People. She enjoys writing and speaking on SQL Server topics, having written over two dozen articles, contributed to four books, and presented at many SQL Server events. In her spare time, Kathi enjoys spending time with family and friends, singing and cycling.

Learn even more about window functions and their power in solving tough query problems from Kathi Kellenberger's book Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Kathi's other books include Beginning SQL Server Reporting Services (2016) and Beginning T-SQL​​​​​​​ (2014).