SQL Server window functions: rows and Range

Original: SQL Server window functions: rows and Range

Almost every time I show a window in SQL Server, people are very interested to know the difference betweenrows and the RANGE option when you define your window (a specified set of rows). So in today's article I want to show you the difference between these options and what it means for your analytical calculations.

the difference between rows and range

when you use the over () clause to perform your analysis calculation to open your window, you can also see in the window, with the rows and RANGE options to limit your number of rows. Look at the following T-SQL statement:

1 SELECT2 T.orderyear,3 T.ordermonth,4 T.totaldue,5     SUM(T.totaldue) Over(ORDER  byT.orderyear, T.ordermonth ROWSbetweenunbounded preceding and  CurrentROW) as 'RunningTotal'6  from7 (8     SELECT9          Year(OrderDate) as 'OrderYear',Ten         MONTH(OrderDate) as 'OrderMonth', One SalesPersonID, A TotalDue -      fromSales.SalesOrderHeader -) asT the WHERE -T.salespersonid= 274 -      andT.orderyear= 2005 - GO

this T-SQL statement is summarized using the sum () aggregate function. The window itself is from line 1th (unbounded preceding) to the current row. For every 1 rows in the record level, the window becomes larger and smaller, making it easy to summarize. Demonstrates this concept.

From the output you can see that the result is a self-growing rollup-the result of running the summary rollup.

Now suppose you modify the window to RANGE between unbounded precedingand current ROW, what happens:

1 SELECT2 T.orderyear,3 T.ordermonth,4 T.totaldue,5     SUM(T.totaldue) Over(ORDER  byT.orderyear, T.ordermonth RANGEbetweenunbounded preceding and  CurrentROW) as 'RunningTotal'6  from7 (8     SELECT9          Year(OrderDate) as 'OrderYear',Ten         MONTH(OrderDate) as 'OrderMonth', One SalesPersonID, A TotalDue -      fromSales.SalesOrderHeader -) asT the WHERE -T.salespersonid= 274 -      andT.orderyear= 2005 - GO

As you can see, you get different results, and the same totals are shown for the November 2005 records.

Let's try to understand why the range option here gives you different results than the ROWS option. Using the rows option, you define a fixed and pre-post record for the current row. The line you see here depends on the ORDER by clause of the window. You can also say that you define your window at the physical level.

when you use the RANGE option, things change. The RANGE option contains all the rows in the window, with the same ORDER by value as the current row. As you can see from the picture just now, you get the same rollup for the 2 records in November 2005 because the 2 rows have the same ORDER by Value (November 2005). Use the RANGE option to define your window at the logical level. If more rows have the same ORDER by value, your window will contain more rows when you use the rows option.

Summary

In today's article you see the difference between the rows and range options when you define a window for your analysis calculation. Use the Rows option to define how many rows you have in your window at the physical level. Using the range option depends on how many rows in the window are included in the order by value. So there's a big difference in performance when you use the range option. I'll discuss these side effects in the next article.

Thanks for your attention!

SQL Server window functions: rows and Range