Window Function Examples for SQL Server

Window (or Windowing) functions are a great way to get different perspectives on a set of data without having to make repeat calls to the server for that data. For example, we can gather the sum of a column and display it side-by-side with the detail-level data, such that “SalesAmount” and “SUM(SalesAmount)” can appear in the same row. We can also do analytical functions like PERCENT_RANK and ranking functions like ROW_NUMBER, all without altering the granularity of the result set or making additional trips to get the same source data again and again.

“Watch as I effortlessly balance two of Val Kilmer’s kidney stones. Quite bully!”

Window functions all use the OVER() clause, which is used to define how the function is evaluated. The OVER() clause accepts three different arguments:

  • PARTITION BY: Resets its counter every time the stated column(s) changes values.
  • ORDER BY: Orders the rows the function will evaluate. This does not order the entire result set, only the way the function proceeds through the rows.
  • ROWS BETWEEN: Specifies how to further limit the rows evaluated by the function.

Let’s pretend we’re looking at simplified data from a weight-lifting competition. Here are some code examples (we’ll do them all in one SELECT statement because adding/removing window functions in no way changes the number of rows we get back):

Considerations for Window Functions

If you don’t have SQL Server 2012 or later, your window function cupboard is pretty bare; SQL Server 2005 through 2008 R2 only allowed PARTITION BY in the OVER clause of an aggregate function, and you got RANK() and ROW_NUMBER(). That was about it. If you’re a developer still on one of those earlier versions, this is a compelling case for moving to 2012 or later. Think how much time you could be saving not writing multiple CTEs and how much faster your queries will go.

Speaking of fast…

By avoiding round-trips to the server for the same data, we reduce I/O on those tables. If we’re hitting indexes, we can really whittle down the reads involved. There is a trade-off, but it’s usually a very favorable one. Window functions require SQL Server to construct the window and compute the function (shown as tasks like Window Spool, Segment, Sequence Project, and Compute Scalar). In doing so, it adds reads to the Worktable. Still, this is generally less expensive than going back to get the source data multiple times, aggregating if necessary, and joining it all together. Also, Worktable exists in tempdb, which — ideally —  is on your fastest storage tier.

Finally, remember that the limitations you put on a window function — PARTITION BY, ORDER BY, or ROWS BETWEEN — are there to apply context to the window function and in no way apply to the result set as a whole. In other words, your SELECT statement will be unaffected by anything you tell a window function to do.

More Window Function Resources

Doug Lane’s webcast and scripts on window functions
Windowing Function Examples for SQL Server
Rolling Averages in SQL Server
Itzik Ben-Gan on How to Use Microsoft SQL Server Window Functions
Books Online: Using the OVER() Clause

1 Comment.

  • […] functions of SQl server are very useful. Many webpage like this one  have written how to use them.  I want to share with you one segment of my sql code to get […]