[SQL Server] CTE vs LEAD/LAG function

[SQL Server] CTE vs LEAD/LAG function

LEAD provides access to a row at a given physical offset that follows the current row.
LAG provides access to a row at a given physical offset that comes before the current row.

Source Data
Script

1 2 3 4 5 6 7 8 9

-- Source Data Query SELECT ProductID, ModifiedDate, StandardCost FROM Production.ProductCostHistory WHERE ProductID IN(711, 712, 713) ORDER BY ProductID, ModifiedDate ;

Result



Query Comparisons
CTE + LEFT JOIN
Script

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

-- Using CTE + LEFT JOIN WITH LAG_LEAD_CTE AS (SELECT ROW_NUMBER() OVER( ORDER BY ProductID, ModifiedDate) AS RN, ProductID, ModifiedDate, StandardCost FROM Production.ProductCostHistory WHERE ProductID IN(711, 712, 713)) SELECT A.ProductID, A.StandardCost, A.ModifiedDate, B.StandardCost AS PrevStandardCost, C.StandardCost AS NextStandardCost FROM LAG_LEAD_CTE A LEFT JOIN LAG_LEAD_CTE B ON A.ProductID = B.ProductID AND A.RN = B.RN + 1 LEFT JOIN LAG_LEAD_CTE C ON A.ProductID = C.ProductID AND A.RN = C.RN - 1 ORDER BY A.ProductID, A.ModifiedDate ;

Result


Use ROW_NUMBER in order to compare row offset. And join with the previous row and the next row.


LEAD, LAG
Script

1 2 3 4 5 6 7 8 9 10 11 12 13

-- Using LEAD, LAG function SELECT ProductID, StandardCost, ModifiedDate, LAG(StandardCost, 1, NULL) OVER(PARTITION BY ProductID ORDER BY ModifiedDate) PrevStandardCost, LEAD(StandardCost, 1, NULL) OVER(PARTITION BY ProductID ORDER BY ModifiedDate) NextStandardCost FROM Production.ProductCostHistory WHERE ProductID IN(711, 712, 713) ORDER BY ProductID, ModifiedDate ;

Result

The result is the same as CTE + LEFT JOIN. But it's way much simpler that that.


Performance Comparisons
CTE + LEFT JOIN

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductCostHistory'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

LEAD, LAG

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductCostHistory'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using LEAD, LAG is way much better than CTE + LEFT JOIN.
(Of course, it depends on the situation, but it general)


References

• LEAD (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15
• LAG (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15