Difference between rows and range

Difference between rows and range

range vs rows in sql server
difference between rows clause and range clause in sql server
range clause vs rows clause in sql server
sql server running total query
running total example in sql server

In this video we will discuss the difference between rows and range in SQL Server. This is continuation to Part 116. Please watch Part 116 from SQL Server tutorial before proceeding.

Let us understand the difference with an example. We will use the following Employees table in this demo.

SQL Script to create the Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Salary int
)
Go

Insert Into Employees Values (1, ‘Mark’, 1000)
Insert Into Employees Values (2, ‘John’, 2000)
Insert Into Employees Values (3, ‘Pam’, 3000)
Insert Into Employees Values (4, ‘Sara’, 4000)
Insert Into Employees Values (5, ‘Todd’, 5000)
Go

Calculate the running total of Salary and display it against every employee row

The following query calculates the running total. We have not specified an explicit value for ROWS or RANGE clause.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary) AS RunningTotal
FROM Employees

So the above query is using the default value which is
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means the above query can be re-written using an explicit value for ROWS or RANGE clause as shown below.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

We can also achieve the same result, by replacing RANGE with ROWS
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

What is the difference between ROWS and RANGE
To understand the difference we need some duplicate values for the Salary column in the Employees table.

Execute the following UPDATE script to introduce duplicate values in the Salary column
Update Employees set Salary = 1000 where Id = 2
Update Employees set Salary = 3000 where Id = 4
Go

Now execute the following query. Notice that we get the running total as expected.
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

The following query uses RANGE instead of ROWS
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

Notice we don’t get the running total as expected.

So, the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity.

All together side by side. The following query shows how running total changes
1. When no value is specified for ROWS or RANGE clause
2. When RANGE clause is used explicitly with it’s default value
3. When ROWS clause is used instead of RANGE clause

SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY Salary) AS [Default],
SUM(Salary) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows]
FROM Employees

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rows-and-range_8.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

Get Paid Taking Pictures
Share