SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 4

This is the fourth post in the series of finding previous row and next row value in SELECT Statement. Read all the blog post before continuing reading this blog post for complete idea.

In the very first part I discussed that performance with the help of CTE is very poor and I encouraged users to use LEAD and LAG function of SQL Server 2012. My friend and SQL Server Expert Szymon Wojcik have written a fantastic post about this subject. I encourage everyone to read that blog post. He has demonstrated that with the help of numbers table, we can further improve the performance of the query.

Now let us compare all the various solutions which I have presented in my earlier blog post.

Solarwinds

Before continuing with the performance comparison, I suggest you to change the database context to AdventureWorks and also enable STATISTICS IO settings on for performance comparison.

USE AdventureWorks2012
GO
SET STATISTICS IO ON;

Query 1 for SQL Server 2012 and later version

SELECT
LAG(p.FirstName) OVER(ORDER BY p.BusinessEntityID) PreviousValue,
    p.FirstName,
    LEAD(p.FirstName) OVER(ORDER BY p.BusinessEntityID) NextValue
FROM Person.Person p
GO

Query 2 for SQL Server 2005+ and later version

WITH CTE AS(
    SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID),
    p.FirstName FROM Person.Person p
)
SELECT
prev.FirstName PreviousValue,
    CTE.FirstName,
    nex.FirstName NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
GO 

Query 3 for SQL Server 2005+ and later version

CREATE TABLE# TempTable(rownum INT, FirstName VARCHAR(256));
INSERT INTO# TempTable(rownum, FirstName)
SELECT
rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID),
    p.FirstName
FROM Person.Person p;
SELECT
prev.FirstName PreviousValue,
    TT.FirstName,
    nex.FirstName NextValue
FROM# TempTable TT
LEFT JOIN# TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN# TempTable nex ON nex.rownum = TT.rownum + 1;
GO 

Query 4 for SQL Server 2000+ and later version

SELECT
rownum = IDENTITY(INT, 1, 1),
    p.FirstName
INTO# TempTable
FROM Person.Person p
ORDER BY p.BusinessEntityID;
SELECT
prev.FirstName PreviousValue,
    TT.FirstName,
    nex.FirstName NextValue
FROM# TempTable TT
LEFT JOIN# TempTable prev ON prev.rownum = TT.rownum - 1
LEFT JOIN# TempTable nex ON nex.rownum = TT.rownum + 1;
GO 

All of the above examples will return the following results as a result.

SQL SERVER - How to Access the Previous Row and Next Row value in SELECT statement? - Part 4 leadlagfunction1

Now let us compare the performance for each of the queries with the help of message displayed in the result set. 

Worktable Logical ReadsPerson Logical ReadTotal Logical Read
Query 1038203820
Query 21977606114601989066
Query 317138203991
Query 421638204036

You can clearly see that Query 1 which is using Lead and Lag functions of SQL Server 2012 has the most optimized performance. This gives you one more reason to upgrade to SQL Server 2012.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? – Part 3
Next Post
SQL SERVER – Select Columns from Stored Procedure Resultset

Related Posts

9 Comments. Leave new

  • Thank you this is very helpful. I’ve needed to do this many times.

    Reply
  • Sir,
    IF my table having column like as
    ID OPCenvat CrCenvat DbCenvat Calculate cenvat
    —————————————————————————-
    1 130 80 30 130+80-30= 180
    2 150 50 20 180+150+50-20=380
    3 200 60 40 400+200+60-40=660
    4 300 100 50 700+300+100-50=1050
    —————————————————————————-
    I want result as calculation of OPCenvat+CrCenvat-DbCenvat for Each record which is as Consider as opening balance for nexrecord. please Guide me as early as possible

    Reply
  • Is there any way to partition or reset the identity sequence based on another column? Let’s say you wanted to partition the data by location

    Reply
  • Gret post!

    Reply
  • Dharmesh Solanki
    December 24, 2015 6:00 pm

    Dear Dave Sir, Hats off to You… Very Useful post!!!

    Reply
  • Really helpful. Thanks

    Reply
  • why you have used the “BusinessEntityID” column here , i am getting error that invalid column.

    Reply

Leave a Reply

Menu