lead function 2012 vs 2008R2 tsql

This is quick example of how we can use the lead function to produce simpler and more efficient code than we were able prior to SQL 2012.

I’ve created a dumbed down version of the package change report, Ive also provided code samples to produce identical datasets using both SQL 2008 and the improved 2012 TSQL code.

In this example what we are going to do is show every tariff that a customer has had together with start and end dates and new tariff name if the customer has changed. This is dummy data using customer 1 and 2 and tariffs A,B,C and D.

The old method involved creating a row number against every row in the dataset in a temporary table and then performing a self join on this table against rownumber = rownumber + 1, the new method is simpler to read and much,much more efficient. It uses the lead function which allows us to refer to the next row in the current partition. The old method performs 6 table scans (1+1 scan per row) whilst the new method produces only 1 scan. (Use set statistics io on to see this)

Here is the code

/*set up some sample data*/

declare @t TABLE (cust int, pack VARCHAR(1), tran_date date)

insert into @t VALUES

(1,’A’,’1-Jan-2013′),
(1,’B’,’1-Apr-2013′),
(1,’C’,’2-May-2013′),
(2,’A’,’6-Feb-2013′),
(2,’C’,’2-May-2013′)

/*old method utilizing a cte and self join*/

;with a as(
Select *,row_number() over (partition by cust order by tran_date ) row  from @t)

select
a.cust ‘customer’
,a.pack as ‘package’
,a.tran_date as ‘start date’
,b.tran_date ‘end date’
,b.pack as ‘new package’

from a
left join a b on a.cust = b.cust and a.row +1 = b.row

/*new method using lead function*/

select
a.cust ‘customer’
,a.pack ‘package’
,a.tran_date ‘start date’
,lead(tran_date) OVER (PARTITION BY cust ORDER BY tran_date) ‘end date’
,lead(pack) over (partition by cust order by tran_date) ‘new package’

from @t a

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: