SQL Server Windowing Functions: ROWS vs. RANGE

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Almost every time that I present about Windowing Functions in SQL Server, people are very interested in knowing the difference between the ROWS and RANGE option when you define your window frame. Therefore I want to show you in today’s blog posting the difference between those options, and what it means to your analytic calculations.

ROWS vs. RANGE – what’s the difference?

When you open a window frame with the OVER() clause to perform your analytic calculations, you can also limit the number of rows you see in the window frame through the ROWS and RANGE option. Just look at the following T-SQL statement:

This T-SQL statement performs a running total calculation with the SUM() aggregate function. The window frame itself goes from the first row (UNBOUNDED PRECEDING) up to the current row (CURRENT ROW). For every row in the result set, the window frame gets larger and larger, and therefore it is very easy to perform a running total calculation. The following picture illustrates this concept.

Running Total Calculation with Windowing Functions in SQL Server

And as you can see from the output, the result is just an ever increasing sum – the result of the running total calculation.

The result of the Running Total Calculation with Windowing Functions

Imagine now what happens to your result when you change the window frame to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

As you can see now from the following picture, you get a different result, because the records for November 2005 shows the same running total sum.

The RANGE option gives you a different result as the ROWS option

Let’s try to understand why the RANGE option gives you here a different result than the ROWS option. With the ROWS option you define a fixed number of rows preceding and following the current row. Which rows you see here through your window frame depends on the ORDER BY clause of the window frame. You can also say that you define your window frame on a physical level.

Things change when you use the RANGE option. The RANGE option includes all the rows within the window frame that have the same ORDER BY values as the current row. As you can see from the previous picture, for the 2 records of November 2005 you get the same sum, because both rows have the same ORDER BY values (November 2005). With the RANGE option you define your window frame on a logical level. If more rows have the same ORDER BY value, your window frame consists of more rows than when you use the ROWS option.

Summary

In today’s blog posting you have seen the difference between the ROWS and RANGE option when you define your window frame for analytic calculations. With the ROWS option you define on a physical level how many rows are included in your window frame. With the RANGE option how many rows are included in the window frame depends on the ORDER BY values. There are also huge differences regarding the performance when you use the RANGE option. I will talk about these side-effects in a subsequent blog posting.

Like or share this blog posting to get the source code.

Thanks for your time,

-Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top