LEAD() Function in SQL 2012 makes life easy

2012 introduced LEAD Function. Lets take a scenario for example. We have a categorytable which has categoryID and CategoryValue. You are required to show another calculated field called NextCategoryValue which is basically the value from next record. Normally what we do is write a CTE or table expression and get the next value. The output should be as follows

Here is the sample code

IF OBJECT_ID('dbo.Category','U') IS NOT NULL

DROP TABLE dbo.Category
GO
CREATE TABLE Category
(
    CategoryID INT
    ,CategoryValue INT
)
 

INSERT INTO Category(CategoryID, CategoryValue)

VALUES (1,1), (1,2), (1,4), (1,5), (2,8), (2,10), (3,11),(3,13)

--Get the next Category Value

;WITH CTE
AS
(
    SELECT CategoryID
            ,CategoryValue
           ,ROW_NUMBER() OVER(ORDER BY CategoryValue) AS Ranges
    FROM dbo.Category
)
SELECT A.CategoryID
    , A.CategoryValue
    , B.CategoryValue AS NextCategoryValue
FROM
CTE A
LEFT JOIN CTE B
ON A.Ranges + 1 = B.Ranges

We can achieve the same functionality with LEAD() function with very less code and with a huge perf. improvement

--USE LEAD() Function to get next category value

SELECT CategoryID
        ,CategoryValue
        ,LEAD(CategoryValue) OVER(ORDER BY CategoryValue) AS NextCategoryValue
FROM dbo.Category

We can also use PARTITION BY in the OVER clause of lead function if we want to get the values based on categoryID or a key value. Here is the execution plan

This is very useful for date ranges and other stuff.