Aunt Kathi's SQL Server Home

I have been presenting and writing about windowing functions for a couple of years. I am always surprised that many people who don’t know what these are may already be writing queries using them. Every time I present on this topic at a SQL Saturday, I get at least one evaluation comment saying that they thought the session would be about the Windows operating system, not T-SQL.

These functions, called window, windowed or windowing, are part of the ANSI SQL standards, not something proprietary to SQL Server or to Microsoft. One thing that windowing functions require is an OVER clause that defines the window or set of rows that the function will operate on. You’ll also find windowing functions in only two places in a query: the SELECT and OVER clauses.

There were two types of windowing functions introduced with SQL Server 2005: the ranking functions, ROW_NUMBER(), RANK, DENSE_RANK() and NTILE() and window aggregates. Almost everyone is using or at least familiar with ROW_NUMBER(), but not many people have heard about window aggregates.

Window aggregates allow you to add an aggregate expression to a query without making it an aggregate query. This makes writing some queries very simple. The unfortunate thing, however, is that this technique doesn’t perform as well as some older methods. Here is an example:

SELECT CustomerID, SalesOrderID, TotalDue,
     SUM(TotalDue) OVER(PARTITION BY CustomerID) CustTotal
FROM Sales.SalesOrderHeader;

WITH cust AS(
    SELECT CustomerID,SUM(TotalDue) AS CustTotal
    FROM Sales.SalesOrderHeader
    GROUP BY CustomerID)
SELECT cust.CustomerID,SalesOrderID,TotalDue,CustTotal
FROM Sales.SalesOrderHeader AS SOH
JOIN cust ON SOH.CustomerID=cust.CustomerID;

The first query adds the windowing aggregate expression SUM(TotalDue) OVER(PARTITION BY CustomerID). This provides a total for each unique CustomerID. The second query is one of the traditional ways to do the same thing. Inside the CTE (Common Table Expression) a query returns a list of the CustomerIDs with the total for each. It is then joined to the table in the outer query to provide the same results. If you take a look at the execution plan or STATISTICS IO, you will see that the second method actually performs better.

To get a total over the entire result set, use the empty parentheses like this: SUM(TotalDue) OVER().

So, while the window aggregate method is so simple, you need to use it with caution. Stay tuned for many more posts about windowing functions. I promise that for the most part, they improve performance.