Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LAG’n’LEAD – New T-SQL Features in SQL Server Denali CTP3

Are you tired of doing self joins, just to get the value of a column in the previus/subsequent row, or maybe even worse you are doing multiple self joins. This is where you will start smiling, let’s have a look at an exampel.

I’ll start by creating some demo data:

CREATE TABLE MonthlyProfit
(
    yearid int,
    Monthid int,
    Profit bigint
)

INSERT INTO MonthlyProfit (yearid, Monthid, Profit)
VALUES
(2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),
(2010,7,1766), (2010,8,9100), (2010,9,-100), (2010,10,50), (2010,11,900000), (2010,12,6575)

What I would like to do now is to write a select statement that gives me Yearid, MonthId, ProfitThisMonth and ProfitLastMonth, so that I’m able to compare my profit with last month. Before SQL Server Denali CTP3 my SQL statement would look something like this:

SELECT
 t1.yearid,
 t1.Monthid,
 t1.Profit as ProfitThisMonth,
 t2.Profit as ProfitLastMonth
FROM MonthlyProfit as t1
LEFT JOIN MonthlyProfit as t2 on (t1.yearid = t2.yearid) AND (t1.Monthid = t2.Monthid+1)

A pretty simple statement by looking at it, but as mentioned earlier this is not possible without the nasty self join.

SQL Server Denali CTP3 finally gives us LAG and LEAD. Here is the description directly stolen from BOL:

Accesses data from a previous row in the same result set without the use of a self-join in Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3). LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

The syntax for LAG and LEAD is the same

LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause)

Let’s have a look at my statement now:

SELECT
 yearid,
 monthid,
 Profit as ProfitThisMonth,
 LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth
FROM MonthlyProfit

No more self joins, and a much more simple statement. Read the full documentation to the LAG and LEAD in BOL.

What about performance?

As described in an earlier blog post, I set IO statistics on and run both queries again. Here is the results:

(12 row(s) affected)
Table 'MonthlyProfit'. Scan count 2, logical reads 13, physical reads 0, read-ahead reads 0,…

(12 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0,…
Table 'MonthlyProfit'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0,

So the conclusion is, we get a much simpler query that also does fewer reads, that’s what I call a WIN-WIN situation.

@ms1333

Comments

Posted by Jakub Dvorak on 2 August 2011

This is feature I was praying for since 2000. I am pure Microsoft guy but it is really funny like Microsoft is copying ranking, analytical and windowing function from Oracle one by one (ROW_NUMBER, OVER, RANK, PARTITION BY etc.). Ranking functions were introduced in SQL 2005, I guess, and another couple of basic analytical functions (LAG and LEAD) are being introduced in 2011. We will have all useful built-in function in SQL Server in 2020 with this speed :( Anyway, very good new similar analytical functions in SQL Server 2011 are FIRST_VALUE and LAST_VALUE which do what is expected - returns first or last value from result set/partition.

Leave a Comment

Please register or log in to leave a comment.