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)
- (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:
- 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:
- Profit as ProfitThisMonth,
- LAG(Profit, 1, 0) OVER(Order by yearid, monthid) as ProfitLastMonth
- FROM MonthlyProfit
What about performance?
- (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.