Difference between rows and range Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 117 of sequel server
tutorial in this video we'll discuss the
difference between rows and range clause
in sequel server this is continuation to
part 1 1 6 so please watch part 1 1 6
before proceeding let's understand the
difference with an example now based on
this employee's table on the left we
want to write a query that's going to
compute running total of the salary
column and then display that running
total against every employee row as you
can see here let's flip over to sequel
so management studio will use this
employees table for the examples in this
demo within our results that we want
named celery and then the running total
to compute the running total we are
going to use the sum aggregate function
and then pass the salary column then
we'll use our favorite both laws within
paranthesis we'll specify the order by
clause let's sort the data by salary
column and let's give this column an
aleeah's let's call this running total
let's execute this query and see what we
get notice we get the running total as
expected
for the first row total it's 1,000 for
the second row total is 3000 it adds
first and second rows together and for
the third row it is 6000 thousand plus
two thousand plus three thousand so we
get the running total as expected
now the most important thing to keep in
mind here is that for the rows or range
Clause we are not specifying explicitly
any value so sequel server is using it's
a default value so what is the default
value for rows or range Clause it is
range between unbounded preceding and
current value we discuss that in detail
in our previous video session that means
we can rewrite this query using its
default value so Auto is salary and then
we can specify its default value so the
default value is range between unbounded
preceding and current row now when we
execute this we should get exactly the
same result
notice the running total is exactly
identical so we can either write this
query like this
without its default value or we can
specify its default value both of these
queries should produce exactly the same
result and we have seen that in action
now we can replace that range clause
with ROS clause and still in this case
we are going to get exactly the same
result let's actually prove that so
instead of this range it Clause I'm
going to use the ROS clause let's
execute this and see what we get
notice we get exactly the same result as
before well the obvious question that
comes to our mind at this point is then
what's the difference between range and
ROS clause the difference is in the way
duplicate rules are treated ROS Clause
treat duplicates as distinct values
whereas the range Clause treats them as
a single entity let's actually prove
this with an example
so first let's introduce some duplicate
values for the salary column to do that
I have this update script right here so
let's execute this now let's select the
data from our employees table and make
sure we have that duplicate data so
notice for the first two employees
salary is 1000 for the third and fourth
employees the salary is 3000 so we do
have duplicate data now let's execute
this query now at the moment notice we
are using rows clause let's see what
result we get notice that it computes
the running total as expected so for the
first row it is 1000 for the second row
it is 2,000 because it adds first and
second rows together and for the third
row it is 5000 because it adds 3,000 to
2,000 we get 5,000 so it's working as
expected
now let's replace this rows with range
and let's execute our query and see what
we get notice now the running total is
not as expected and for the first two
rows it's displaying the same running
total mm why is that
that's because range Clause is treating
duplicates as a single entity and the
same is the case for rows 3 & 4 look at
that it displays 8,000 as the running
total that's because it is adding these
two together and when we add 3 & 4
together we get 6000
when we add that to mm we get 8,000 and
that running total is displayed for both
rows three and four so that's the
fundamental difference between rows and
range Clause and we can see those
examples right here here we are using
rows clause so we get the running total
as expected when we use the range Clause
notice that since the duplicates are
treated as a single entity we don't get
the running total as expected now here
we have the query that shows all - all
the three side-by-side so this query
right here you know is showing this
running totals when no value is
specified for rows or range class so
here you know the first line here where
we are using the sum function notice
that we are not specifying any explicit
value for the rows or range Clause that
means it's using its default value and
here we are for the second sum function
call we are explicitly specifying the
value 4 rows or range Clause and on the
third line instead of using range we are
using rows clause and see only you know
this third call to the sum function uses
the correct running total as expected
whereas the other two doesn't produce
the running total that we expect thank
you for listening and have a great day