The Power of Window Functions

Window functions are the most efficient way to calculate certain aggregates such as running totals.

For example, we could use the CROSS APPLY operator to calculate the running total, as shown below, using the sample database Wide World Importers

 
SELECT t1.CustomerID, t1.InvoiceID, t1.TransactionAmount
,c.RunningTotali
FROM [WideWorldImporters].[Sales].[CustomerTransactions] t1
CROSS APPLY(
SELECT SUM(t2.TransactionAmount) RunningTotal
FROM [WideWorldImporters].[Sales].[CustomerTransactions] t2
WHERE t2.InvoiceID is not null
AND t2.InvoiceID <= t1.InvoiceID
AND t1.CustomerID = t2.CustomerID
) c
WHERE t1.InvoiceID is not null
ORDER BY t1.CustomerID, t1.InvoiceID

We could refactor this query to use window functions, as shown below

 
SELECT t1.CustomerID, t1.InvoiceID, t1.TransactionAmount,
SUM(t1.TransactionAmount) OVER(PARTITION BY t1.CustomerID
ORDER BY t1.InvoiceID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) RunningTotal
FROM [WideWorldImporters].[Sales].[CustomerTransactions] t1
WHERE t1.InvoiceID is not null
ORDER BY t1.CustomerID, t1.InvoiceID

Both queries will get the same result

However, the performance difference is abysmal

As shown above, the CROSS APPLY query (first statement) takes about 5 min and 2 million reads, while the window function (second statement) takes 264 milliseconds and a thousand reads to complete

Enjoy the power.