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.Â
Worktable Logical Reads | Person Logical Read | Total Logical Read | |
Query 1 | 0 | 3820 | 3820 |
Query 2 | 1977606 | 11460 | 1989066 |
Query 3 | 171 | 3820 | 3991 |
Query 4 | 216 | 3820 | 4036 |
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)
9 Comments. Leave new
Thank you this is very helpful. I’ve needed to do this many times.
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
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
Gret post!
Thanks nicuzzo.
Dear Dave Sir, Hats off to You… Very Useful post!!!
Thanks Dharmesh
Really helpful. Thanks
why you have used the “BusinessEntityID” column here , i am getting error that invalid column.