Lead and Lag functions in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 114 of sequel server
tutorial in this video we'll discuss
lead and lag functions in sequel server
both of these functions are introduced
in sequel server 2012 lead function is
used to access subsequent row data along
with the current row data lag function
on the other hand is used to access
previous row data along with the current
row data
with both of these functions order by
Clause is required partition by clause
is optional we have this index here
first we specify the name of the
function lead or lag both these
functions have got three parameters out
of these three parameters only the first
parameter column name is required the
rest of the two parameters are optional
so the first parameter column name
specifies the column value that we want
to lead or lag the second parameter is
offset this parameter specifies the
number of rows to lead or lag if you
don't specify a value for this parameter
the default will be 1 the third
parameter is default value which
specifies what value to return if the
number of rules to lead or lag goes
beyond first row or last row in a table
or a partition if the default value is
not specified because it is optional
null will be returned followed by that
we use the over Clause and then within
Francis we specify order by and then the
column list by which we want to sort the
data let's look at a couple of examples
now let's flip to sequencer management
studio will use this employee's table
within our results that we want name
gender salary and then let's use the
lead function with the lead function
only the first parameter column name is
required so we want to lead or lag
salary column value so we specify the
column name and then followed by that we
use the over keyword and then within
Francis we use order by and then let's
sort the data by salary column let's
give this column an Aaliyah's let's call
this lead let's execute this query and
see what we get so basically here we
haven't specified offset parameter value
right so if we don't specify a value for
of set parameter the default is going to
be 1 so what does lead one does so we
are on the first row right here and the
column that we have specified here is
salary meaning we want to lead or lag
salary column value and we have sorted
this data by salary column in ascending
order okay so we are on the first row
marks salary is 1,000 and the default
value for offset here is 1 so we want to
lead one row so what this is going to do
is it's going to look up the next row
data so within the next row what is the
salary column value 2,000 and that's
what is displayed in this lead column
right here so while we are on the
current row we are able to lead that is
we are able to look down one row because
we have specified offset value as one
and then take the salary column value
and display that in the current row so
while we are on the current row we have
access to subsequence Roo data depending
on the value that you have specified for
offset parameter so similarly when we
are on the second row we are able to
retrieve the third row salary column
value and display that within the
current row similarly when we are on
this last row look at this we don't have
a row beyond this last row in this table
so that's why we get null again you know
the third parameter default value is
optional if we don't specify it and if
there is no row to lead or lag
you know this function is going to
return now now if you don't want null if
you want a default value for that you
can specify what is that default value
okay so now instead of relying on the
defaults let's specify an explicit value
for offset so let's say we want to lead
to ROS and the default value is going to
be minus 1 now let's execute this query
and see what we get now it should lead
to rows from the current row so when we
are on first row it's going to look down
two rows so it's going to go to the
third row retrieve its salary column
value and display that within the
current row
okay and look at what happened when we
are on the rule nine so lead of two is
basically saying look down two rows but
we don't have two rows beyond this rule
ten there were no more rows so it's
going to return null but since we have
disavowed a default value of minus one
that's what is displayed here instead of
now okay now let's see what happens when
we use lag function so lead function
allows us to look down whereas lag
function allows us to look back now
let's say I want to lag by one row and
in case if there are no rules to lag we
want this minus one as the default value
instead of now okay let's change the
column name here to lag let's execute
this and see what we get so look at this
we are on the first row lag of one is
basically telling look what we have
before this row so this is the first row
in the table there's nothing else to
look back so this is going to return
null but since we specify the default
value of minus one that's what is
returned and if you look at this last
row here the previous row for this last
row is this one and the value there is
9000 and that's what is displayed right
here so that's what lead and lag
functions does okay at the moment we
don't have any partitions in the table
now we can also use partition by Clause
along with lead or lag functions let's
see what's going to happen when we
partition the data by using the
partition by clause let's partition the
data by gender column let's do the same
thing for lag function as well let's
execute this and see what we get all
right first of all notice the data in
the result set is partitioned into two
partitions female and male first let's
look at the female employees partition
so we are on the first row at the moment
and we are saying lead to Rose so it's
going to look up the third row salary
column value which is 6000 that will be
displayed here and then when we are on
the second row it's again going to look
up so four through eight thousand that's
what we can't when we are on the third
row
it tries to look up you know the second
row now if you look at this female
employees partition we don't have a
second row when we are on the third row
within that partition it crosses the
partition okay so that's why it is going
to return now and since we have
specified a default value it returns -1
same is the case for this row as well
and lag function is also going to work
exactly you know like the lead function
within a partition but instead of
looking forward it will look back so we
have our first example here without
partitions and our second example with
partitions thank you for listening and
have a great day