Understanding Lead and Lag Functions in SQL Server 2012

 Lead :

          functions access data from a subsequent row (lead) 

 Lag   :

      functions access data from a previous row (Lag )

   Go through following script.

Create table #sk_lead_leg(

[id] [int] IDENTITY(1,1) , 

[Department] [nchar](100) NOT NULL,

[Code] [int] NOT NULL

)

go

insert into #sk_lead_leg(Department ,Code )

values 

('X',100),

('Y',200),

('Z',300)

 go

 select * from #sk_lead_leg order by id asc

 go

Go

SELECT id,Department,Code,

LEAD(Code,1) OVER (ORDER BY Code ) LeadValue,

LAG(Code,1) OVER (ORDER BY Code ) LagValue

FROM #sk_lead_leg

Go

  We can see the output.

Lead :  Last value is null  because it  is started from second value.

Lag : First Value is null because it is started before first value.