Difference between rows and range

Suggested Videos
Part 114 - Lead and Lag functions in SQL Server 2012
Part 115 - FIRST_VALUE function in SQL Server
Part 116 - Window functions 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

You get the following result when you execute the above query. 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