I often heard in various forums about the use of lead and Lag
Function, since most of the developers are still working on 2008 R2, so they
are not aware of these two Lead and Lag functions.Below I will explain the Lead & Lag Function with a simple example
SQL Server 2012
introduces new analytical function LEAD() and LAG(). These functions accesses
data from a subsequent row (for lead) and previous row (for lag) in the same
result set without the use of a self-join.
First I will explain what exactly Lead and Lag is with an
example
Below is a list of 5 records from A to E
A, B, C, D and E
As per Lead definition- “Lead Accesses data from a
subsequent row in the same result set without the use of a self-join”
So in our example Since A is the first
value so B is leading A Similarly C is leading B Next D is leading C and E is
leading D and since E is the last Record and doesn’t have any subsequent Row so
the Lead Value of E is NULL as shown in below table structure
Name LeadValue
A B
B C
C D
D E
E NULL
Now using the same example I will show the
Lag values
As per LAG
definition- “Accesses
data from a previous row in the same result set without the use of a self-join”
So in our example Since A is the first
value so A doesn’t have any lag value so it will have LAG Value as NULL. Now
Since A is coming before B so B has a lag value as A Similarly C has lag values
as B, next D has a Lag value which is C, and E has lag value which is D as
shown in below table structure
Name LagValue
A NULL
B A
C B
D C
E D
Now, I will use the above example to use
Lead and Lag Function in SQL Server
--Test table Script with above example
create table #t(id int identity(1,1), name varchar(100))
insert into #t values('A'),('B'),('C'),('D'),('E')
--Using
Lead & Lag Function to Get the Leading & Lagging value
SELECT *,
LEAD(Name) OVER (ORDER BY ID) LeadValue,
LAG (Name) OVER (ORDER BY ID) LagValue
FROM #t
This is just a basic and very simple
example of Lead and Lag function, in real time scenario’s there will be many situations
where these functions are quite handy.
NOTE: Lead & Lag functions only works with SQL 2012 &
above versions
