HomeОбразованиеRelated VideosMore From: kudvenkat

Difference between rows and range

156 ratings | 32824 views
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
Html code for embedding videos on your blog
Text Comments (15)
Simon White (2 months ago)
Great videos Venkat, they have been immensely useful. For the benefit of others, just wanted to clarify something that initially confused me. On the last slide, from 4:55, you'd expect the Default column to show an accurate running total like the Rows column is doing. If the Salaries were incremental, it does work like this, but since there are duplicates, the ORDER BY clause - which only specifies Salary - can't tell which of the duplicate Salaries comes first, so has to show the SUM for both.
Murlimohan Mahalanka (2 months ago)
Hi Venkat your videos are good. I follow all the videos of you. IN the last screen short you done mistake. The Default column contain wrong values. In this video you can see ur first screen short and last screen short.
Thank's!
Krzysztof S (1 year ago)
Venkat rules! Thank U for all your achievements ! Thank U for educating community. You way , style of teaching is awesome and superb! You are the best teacher on the planet . Thanks a lot ! Greetings from beautiful country Poland!
archrodney (2 years ago)
Great tutorial, I would like to add that RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters, so you cannot use RANGE for a number of rows like in this example: "RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING" . This will give you an error but you can use ROWS instead.
phanesh padki (2 years ago)
nicely explained... not just this video.. all other videos are also very good.... nice job...:)
Justin Li (2 years ago)
as awesome as always
i think there is some typos in the last slider in the Default column where it should you the default value which is ( unbound preceding and current row ).. please correct me if I misunderstood it
Frenky B. (1 month ago)
Perhaps you think of calculated column of salary summary? I've noticed that, too.
Kamalpreet Kaur (3 years ago)
hi venkat...ur videos are just awesome....can u plz make videos on composable DML and on querying and managing XML data in SQL server 2012? Keep doing the good work.God bless u .
raqibul1000000 Alam (3 years ago)
Thanka a lot
raqibul1000000 Alam (3 years ago)
+kudvenkat Dear Sir Thanks a billion for your reply
kudvenkat (3 years ago)
+raqibul1000000 Alam Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Dot Net & SQL Server training videos for web developers https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd You can order DVDs for offline viewing using the link below http://www.pragimtech.com/Order.aspx Code Samples & Slides are on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use our channel https://www.youtube.com/watch?v=y780MwhY70s To receive email alerts, when new videos are uploaded, please subscribe to our channel http://www.youtube.com/subscription_center?add_user=kudvenkat Please click that THUMBS UP button below the video, if you like the videos Thanks a million for sharing these resources with your friends Best Venkat
Alluri Kumar (3 years ago)
please make a video on performance tuning in sql server it is very useful to many please make a video
Alluri Kumar (3 years ago)
hi venkat please make a video on performance tuning in sql server

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.