SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – from Pinal Dave

Szymon Wójcik:

After reading forementioned article by Pinal Dave I wanted to compare those methods and I even managed to make the CTE version to run in acceptable time – around 0.5 seconds on 20k records in AW2012 Person table (the original runs over 95 seconds). The trick is to use numbers table – I didn’t bother creating a new one, so I used another CTE that created 1 million of numbers.
Worth noting is the fact that it still is nowhere near the performance of LAG/LEAD functions in 2012, mostly due to I/O required.

with Numbers as
 select (v1.number * 1024) + v2.number as Num
 master.dbo.spt_values v1
 inner join master.dbo.spt_values v2 on v1.type = v2.type
 where v1.type = 'P' and v1.number < 1024 and v2.number < 1024
 (select Num - 1 Prev, Num, Num + 1 Nex
 from Numbers),
 rownum = ROW_NUMBER() OVER (ORDER BY p.BusinessEntityID),
 FROM Person.Person p)
 select th1.FirstName, th2.FirstName, th3.FirstName
 from ThisPrevNext tpn
 left join CTE th1 on th1.rownum = tpn.Prev
 left join CTE th2 on th2.rownum = tpn.Num
 left join CTE th3 on th3.rownum = tpn.Nex
 where tpn.Num between 1 and (select count(*) from CTE)

The reason for that is that CTE version has to scan underlying table several times, while LEAD/LAG do it in one pass. While the difference is negligible in case of 20k records (on my test VM it’s 172 ms vs 533 ms), it will be clearly visible when the table is larger. I copied Person.Person table into a new table and duplicated it several times so that it contains 639k records.

The results are as follows:

  1. LEAD/LAG – ~4 seconds, 18630 reads
  2. CTE with numbers table – ~35 seconds, 59192 reads
  3. original CTE – after 50 minutes I cancelled the query as it was not yet 20% done :)

The outcome is to use LEAD/LAG where possible – if you consider upgrading to 2012 and require such functionality, this might be one of your arguments.

Originally posted on Journey to SQL Authority with Pinal Dave:

The first email I read this morning had only one statement in it, and it gave me an idea to write this blog post.

“How to access Previous Row Value and Next Row Value in SELECT statement?”

Very interesting question indeed. The matter of the fact, I have written about this in my earlier blog Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012. Let us quickly understand it in it with the help of script.

For example here is the column where we want to get access to the previous row and the next row in SELECT statement.

USE AdventureWorks2012
SELECT p.FirstName
FROM Person.Person p
ORDER BY p.BusinessEntityID

Now let us use the new Analytic Function from SQL Server 2012 to get previous and next row values.

LAG(p.FirstName) OVER (ORDER BY p.BusinessEntityID) PreviousValue,

View original 148 more words

About these ads

One thought on “SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – from Pinal Dave

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