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.

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

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.

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.

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

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)