Sql server® 2012 t-sql fundamentals

Window Functions

A window function is a function that, for each row, computes a scalar result value based on a calcula-

tion against a subset of the rows from the underlying query. The subset of rows is known as a window 

and is based on a window descriptor that relates to the current row. The syntax for window functions 

uses a clause called OVER, in which you provide the window specification.

If this sounds too technical, simply think of the need to perform a calculation against a set and 

return a single value. A classic example would be aggregate calculations such as SUM, COUNT, and 

AVG, but there are others as well, such as ranking functions. If you’re reading this chapter, you should 

be familiar already with a couple of ways to apply such calculations—one is by using grouped queries, 

and another is by using subqueries. However, both options have shortcomings that window functions 

elegantly resolve.

Grouped queries do provide insights into new information in the form of aggregates, but they also 

cause you to lose something—the detail. After you group the rows, all computations in the query 

have to be done in the context of the defined groups. Often you need to perform calculations that 

involve both a detail element and the result of a set calculation such as an aggregate. Window func-

tions are not limited in the same way. A window function has an OVER clause that defines the set of 

rows for the function to work with, without imposing the same arrangement of rows on the query 

www.it-ebooks.info

---

212  

Microsoft SQL Server 2012 T-SQL Fundamentals

itself. In other words, grouped queries define the sets, or groups, in the query, and therefore all calcu-

lations in the query have to be done in the context of those groups. With window functions, the set is 

defined for each function, not for the entire query.

As for subqueries, they do allow you to apply a calculation against a set, but a subquery starts 

from a fresh view of the data. If the query has table operators or filters, for example, and you need 

the subquery to operate on a subset of rows from the underlying query, you have to repeat a lot of 

logic from the underlying query also in the subquery. In contrast, a window function is applied to a 

subset of rows from the underlying query’s result set—not a fresh view of the data. Therefore, any-

thing you add to the underlying query is automatically applicable to all window functions used in the 

query. Then, different elements in the window function’s OVER clause allow you to further restrict the 

window as a subset of the underlying query’s result set.

Another benefit of window functions is the ability to define order, when applicable, as part of the 

specification of the calculation, without conflicting with relational aspects of the result set. That is, or-

der is defined for the calculation, and not confused with presentation ordering. The ordering specifi-

cation for the window function, if applicable, is different from the ordering specification for presenta-

tion. If you don’t include a presentation ORDER BY clause, there are no assurances that the result will 

be returned in a particular order. If you do decide to force certain presentation ordering, the resulting 

ordering can be different than the ordering for the window function.

Following is an example of a query against the Sales.EmpOrders view in the TSQL2012 database that 

uses a window aggregate function to compute the running total values for each employee and month.

USE TSQL2012; 

 

SELECT empid, ordermonth, val, 

  SUM(val) OVER(PARTITION BY empid 

                ORDER BY ordermonth 

                ROWS BETWEEN UNBOUNDED PRECEDING 

                         AND CURRENT ROW) AS runval 

FROM Sales.EmpOrders;

Here’s the output of this query, shown in abbreviated form.

empid  ordermonth  val      runval 

------ ----------- -------- ---------- 

1      2006-07-01  1614.88  1614.88 

1      2006-08-01  5555.90  7170.78 

1      2006-09-01  6651.00  13821.78 

1      2006-10-01  3933.18  17754.96 

1      2006-11-01  9562.65  27317.61 

... 

2      2006-07-01  1176.00  1176.00 

2      2006-08-01  1814.00  2990.00 

2      2006-09-01  2950.80  5940.80 

2      2006-10-01  5164.00  11104.80 

2      2006-11-01  4614.58  15719.38 

... 

 

(192 row(s) affected)

www.it-ebooks.info

---