Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.
range vs rows in sql server

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
sql server running total query

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

running total example in sql server

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.
 range clause vs rows clause in sql server

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

calculate cumulative total in sql server

1 comment:

  1. Hi sir,

    I need to differences between Model first, schma first, code first approches i you have any video please give
    Thanks & regards
    Pramod

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.