Sql server® 2012 t-sql fundamentals

a Quick Look at Window Functions

A window function is a function that, for each row in the underlying query, operates on a window 

(set) of rows and computes a scalar (single) result value. The window of rows is defined by using an 

OVER clause. Window functions are very profound and allow you to address a wide variety of needs. 

There are several categories of window functions that SQL Server supports, and each category sup-

ports several different functions. However, at this point in the book, it could be premature to get into 

too much detail. So for now, I’ll provide just a glimpse of the concept, and demonstrate it by using 

the ROW_NUMBER window function. Later in the book, in Chapter 7, “Beyond the Fundamentals of 

Querying,” I provide more details.

As mentioned, a window function operates on a set of rows exposed to it by a clause called OVER. 

For each row in the underlying query, the OVER clause exposes to the function a subset of the rows 

from the underlying query’s result set. The OVER clause can restrict the rows in the window by using 

the PARTITION BY subclause, and it can define ordering for the calculation (if relevant) by using the 

ORDER BY subclause (not to be confused with the query’s presentation ORDER BY clause).

Consider the following query as an example.

SELECT orderid, custid, val, 

  ROW_NUMBER() OVER(PARTITION BY custid 

                    ORDER BY val) AS rownum 

FROM Sales.OrderValues 

ORDER BY custid, val;

www.it-ebooks.info

---