What’s the difference between ROWS and RANGE? Explained!

Reading Time: 5 minutes

Window functions are a great thing to know when querying data in a Microsoft SQL Server database.

If you need an introduction to SQL Server window functions, you should definitely check out my full tutorial on the topic:

SQL Server Window Functions: An introduction for beginners

Also, all the information in this tutorial can be found in the FREE SQL Server Window Functions Ebook!

This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This EBook will definitely be a great resource for you to reference throughout your career. Get it today!

This tutorial assumes you know a thing or two about window functions. Specifically, you understand how important a window frame is when writing a window query.

When writing a window frame, you might have seen that you can use one of two window units: ROWS or RANGE

In this very brief tutorial, we’ll outline the difference between the two. It’s simple:

RANGE includes peers while ROWS does not.

That’s the short answer, but let’s talk about what this means.

Take a look at the following query that gives us a running total of purchases made by each customer for our business:

I’ll quickly go over what we’re looking at. We’re basically looking at a running total of purchases made for each customer, in the order that the purchases were made. We use the PARTITION clause to give us the customer groupings. Then we use the frame ROWS UNBOUNDED PRECEDING combined with the SUM aggregate window function to give us the running total values.

Again folks, if you have no idea what I’m talking about, you should definitely check out my introduction to window functions. You can also find the queries to create all the data in your environment if you want to follow along.

Let’s take a look at the rows for our customer Gordon Acres specifically:

Let’s talk about the values in our RunningTotal column. The first row represents the only item he had purchased up until that point, $220.00.

The second row represents what he bought that time plus what he bought the time before. $265.00 + $220.00 = $485.20.

Finally, the third row represents what he bought that time plus what he bought the time before that, plus what he bought the time before that. $169.40 + $265.20 + $220.00 = $654.60.

Think about your ordering values

Take a look at the first two rows for Gordon Acres, and understand that our window function has a window order clause that is ORDER BY O.OrderDate.

The first two rows have the same OrderDate value, 8/6/2021. When two or more rows have the same ordering value, we say they are peers. In our case, the first two rows for Gordon Acres are peers because they have the same OrderDate value of 8/6/2021.

The ROWS window unit does not include peers when figuring out the output of our window function.

RANGE does include peers. Take a look at the same query using RANGE:

If you think about it, Gordon Acres did spend a total of $485.20 on that date. So if we want a running total of purchases made by date, maybe it’s more appropriate to show that price twice.

Know what I mean?

So the choice between ROWS and RANGE is entirely up to what you want to see!

Things you should know

There are some other housekeeping differences between ROWS and RANGE you need to know.

1) With the ROWS unit, you can use the following frame delimiters:

UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
N PRECEDING
N FOLLOWING

The RANGE unit only supports a subset of those delimiters:

UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

2) Understand that the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , or just RANGE UNBOUNDED PRECEDING for short. So if you leave out the frame, understanding that it will be including peers by default.

3) Lastly, Microsoft makes it very clear that the RANGE unit is less efficient than the ROWS unit. So if you must use RANGE (either intentionally or by using the default frame), understand that your query will take a hit in performance.

Links:

If you found this tutorial helpful, make sure you…

Download your FREE SQL Server Window Functions EBook!

Next Steps:

Leave a comment if you found this tutorial helpful!

I hope you enjoyed this very brief tutorial on the ROWS and RANGE window units.

If you need a full rundown on window functions, check out my full introduction to the topic here:

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!

What’s the difference between ROWS and RANGE? Explained!

SQL Server Window Functions: An introduction for beginners