{"id":79473,"date":"2018-06-27T13:57:52","date_gmt":"2018-06-27T13:57:52","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=79473"},"modified":"2026-03-12T15:39:12","modified_gmt":"2026-03-12T15:39:12","slug":"t-sql-window-functions-and-performance","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/t-sql-window-functions-and-performance\/","title":{"rendered":"T-SQL Window Functions: Performance, NTILE & Framing"},"content":{"rendered":"\n

T-SQL window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM OVER, etc.) can create performance bottlenecks if you don\u2019t understand sorting. Every window function with ORDER BY in the OVER clause requires SQL Server to sort the data – each different OVER clause may require a separate sort. The keys to performance: (1) align OVER clause ORDER BY with existing index order to eliminate sorts, (2) understand framing (ROWS vs. RANGE) because RANGE is the default and requires extra work, (3) avoid multiple OVER clauses with different ORDER BY columns, and (4) pre-aggregate for unsupported functions like COUNT(DISTINCT).<\/p>\n\n\n\n

T-SQL window functions make writing many queries easier, and they often provide better performance as well over older techniques. For example, using the LAG<\/strong> function is so much better than doing a self-join. To get better performance overall, however, you need to understand the concept of framing and how window functions rely on sorting to provide the results.<\/p>\n\n\n\n

NOTE: See my new article<\/a> to learn how improvements to the optimizer in 2019 affect performance! <\/em><\/p>\n\n\n\n

The OVER Clause and Sorting<\/h2>\n\n\n\n

There are two options in the OVER<\/strong> clause that can cause sorting: PARTITION BY<\/strong> and ORDER BY<\/strong>. PARTITION BY<\/strong> is supported by all window functions, but it\u2019s optional. The ORDER BY<\/strong> is required for most of the functions. Depending on what you are trying to accomplish, the data will be sorted based on the OVER<\/strong> clause, and that could be the performance bottleneck of your query.<\/p>\n\n\n\n

The ORDER BY<\/strong> option in the OVER <\/strong>clause is required so that the database engine can line up the rows, so to speak, in order to apply the function in the correct order. For example, say you want the ROW_NUMBER<\/strong> function to be applied in order of SalesOrderID<\/strong>. The results will look different than if you want the function applied in order of TotalDue<\/strong> in descending order. Here is an example:<\/p>\n\n\n\n

USE AdventureWorks2017; --or whichever version you have\nGO\nSELECT SalesOrderID, \n\tTotalDue, \n\tROW_NUMBER() OVER(ORDER BY SalesOrderID) AS RowNum\nFROM Sales.SalesOrderHeader;\n\nSELECT SalesOrderID, \n\tTotalDue, \n\tROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum\nFROM Sales.SalesOrderHeader;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Since the first query is using the cluster key as the ORDER BY<\/strong> option, no sorting is necessary.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The second query has an expensive sort operation.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The ORDER BY<\/strong> in the OVER<\/strong> clause is not connected to the ORDER BY<\/strong> clause added to the overall query which could be quite different. Here is an example showing what happens if the two are different:<\/p>\n\n\n\n
SELECT SalesOrderID, \n\tTotalDue, \n\tROW_NUMBER() OVER(ORDER BY TotalDue DESC) AS RowNum\nFROM Sales.SalesOrderHeader\nORDER BY SalesOrderID;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
 The clustered index key is SalesOrderID<\/strong>, but the rows must first be sorted by TotalDue<\/strong> in descending order and then back to SalesOrderID<\/strong>. Take a look at the execution plan:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The PARTITION BY<\/strong> clause, supported but optional, for all T-SQL window functions also causes sorting. It\u2019s similar to, but not exactly like, the GROUP BY<\/strong> clause for aggregate queries. This example starts the row numbers over for each customer.<\/p>\n\n\n\n
SELECT CustomerID,\n\tSalesOrderID, \n\tTotalDue, \n\tROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) \n          AS RowNum\nFROM Sales.SalesOrderHeader;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The execution plan shows just one sort operation, a combination of CustomerID<\/strong> and SalesOrderID<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The only way to overcome the performance impact of sorting is to create an index specifically for the OVER<\/strong> clause. In his book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions<\/a>, Itzik Ben-Gan recommends the POC index. POC stands for (P)ARTITION BY<\/strong>, (O)RDER BY<\/strong>, and (c)overing. He recommends adding any columns used for filtering before the PARTITION BY<\/strong> and ORDER BY<\/strong> columns in the key. Then add any additional columns needed to create a covering index as included columns. Just like anything else, you will need to test to see how such an index impacts your query and overall workload. Of course, you cannot add an index for every query that you write, but if the performance of a particular query that uses a window function is important, you can try out this advice.<\/p>\n\n\n\n
Here is an index that will improve the previous query:<\/p>\n\n\n\n
CREATE NONCLUSTERED INDEX test ON Sales.SalesOrderHeader\n(CustomerID, SalesOrderID) \nINCLUDE (TotalDue);<\/pre>\n\n\n\n
When you rerun the query, the sort operation is now gone from the execution plan:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Read Also: <\/strong>Closure tables for hierarchical queries<\/a><\/p>\n\n\n\n
Framing<\/h2>\n\n\n\n
In my opinion, framing is the most difficult concept to understand when learning about T-SQL window functions. To learn more about the syntax see introduction to T-SQL window functions<\/a>. Framing is required for the following:<\/p>\n\n\n
\n