SQL Server Windowing Functions: ROWS vs. RANGE

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:

SELECT
	t.OrderYear,
	t.OrderMonth,
	t.TotalDue,
	SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
	SELECT
		YEAR(OrderDate) AS 'OrderYear',
		MONTH(OrderDate) AS 'OrderMonth',
		SalesPersonID,
		TotalDue
	FROM Sales.SalesOrderHeader 
) AS t
WHERE
	t.SalesPersonID = 274
	AND t.OrderYear = 2005
GO

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.

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

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

SELECT
	t.OrderYear,
	t.OrderMonth,
	t.TotalDue,
	SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
	SELECT
		YEAR(OrderDate) AS 'OrderYear',
		MONTH(OrderDate) AS 'OrderMonth',
		SalesPersonID,
		TotalDue
	FROM Sales.SalesOrderHeader 
) AS t
WHERE
	t.SalesPersonID = 274
	AND t.OrderYear = 2005
GO

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.

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.

Thanks for your time,

-Klaus