Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 213

The window specification in the OVER clause has three main parts: partitioning, ordering, and 

framing. An empty OVER() clause exposes to the function a window made of all rows from the under-

lying query’s result set. Then anything you add to the window specification essentially further restricts 

the window. 

The window partition clause (PARTITION BY) restricts the window to the subset of rows from the 

underlying query’s result set that share the same values in the partitioning columns as in the current 

row. In the example, the window is partitioned by empid. Consider, for example, a row in which the 

empid value is 1. The window exposed to the function in respect to that row will have only the subset 

of rows in which the empid value is 1.

The window order clause (ORDER BY) defines ordering in the window, but don’t confuse this with 

presentation ordering; the window ordering is what gives meaning to window framing. In this case, 

the window ordering is based on ordermonth.

After order has been defined in the window, a window frame clause (ROWS BETWEEN

delimiter> AND ) filters a frame, or a subset, of rows from the window partition 

between the two specified delimiters. In this example, the frame is between the beginning of the 

par tition  (UNBOUNDED PRECEDING) and the current row (CURRENT ROW). In addition to the window 

frame unit ROWS, there’s another called RANGE, but it was implemented in a very limited form as of 

Microsoft SQL Server 2012.

Putting all of these together, what you get from the function in the example is the running total 

values for each employee and month.

Note that because the starting point of a window function is the underlying query’s result set, 

and the underlying query’s result set is generated only when you reach the SELECT phase, window 

functions are allowed only in the SELECT and ORDER BY clauses of a query. If you need to refer to 

a window function in an earlier logical query processing phase (such as WHERE), you need to use a 

table expression. You specify the window function in the SELECT list of the inner query and assign it 

with an alias. Then in the outer query, you can refer to that alias anywhere you like.

As with any new concept, the windowing concept can take some getting used to, but when you 

are comfortable with it, you’ll realize that it’s actually much better aligned with the way we humans 

tend to think of calculations. Hence, in the long run, window functions will allow you to phrase what 

you want in a natural and intuitive manner. Window functions also lend themselves to very efficient 

optimization for common-use cases.

There were two major milestones in SQL Server’s support for window functions. SQL Server 2005 

introduced ranking window functions with complete implementation (partitioning and ordering), and 

partial support for window aggregate functions (only partitioning, without ordering and framing). 

SQL Server 2012 adds a lot of functionality, including support for ordering and framing for aggre-

gates, as well as new types of functions: offset and distribution. There are still standard windowing 

capabilities that were not yet implemented in SQL Server, and I hope very much to see Microsoft 

continuing the investment in this area.

www.it-ebooks.info

---

214  

Microsoft SQL Server 2012 T-SQL Fundamentals

In the next sections, I provide more specifics about ranking, offset, and aggregate window func-

tions. Because this book is about fundamentals, there are some things that I will not get into here. 

Those include optimization of window functions, distribution functions, and the RANGE window 

frame unit.