Welcome to the fourth post in our series on finding previous row and next row values in a SELECT statement. If you haven’t read the previous posts, I recommend doing so before continuing with this one to get a complete understanding of the topic.

In the first part of this series, we discussed how the performance of using Common Table Expressions (CTE) is poor and encouraged users to utilize the LEAD and LAG functions introduced in SQL Server 2012. Our SQL Server expert, Szymon Wojcik, has written a fantastic blog post on this subject, which I highly recommend reading.

In his post, Szymon demonstrated that by using a numbers table, we can further improve the performance of the query. Now, let’s compare all the various solutions that I have presented in my earlier blog posts.

Before we continue with the performance comparison, I suggest changing the database context to AdventureWorks and enabling STATISTICS IO settings for accurate performance measurement.

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 same results. Now, let’s compare the performance of each query using the message displayed in the result set.

As you can see, Query 1, which utilizes the Lead and Lag functions of SQL Server 2012, has the most optimized performance. This provides yet another reason to consider upgrading to SQL Server 2012.

Stay tuned for more informative blog posts on SQL Server concepts and ideas!