SQL LEAD Function in SQL Server 2012 to Calculate Next Value
SQL LEAD() function is one of the recent enhancements among SQL Analytic Functions introduced with SQL Server 2012 (aka Denali, CTP3) for developers. What SQL Server Lead() function does is returning simply the next Nth row value in an order. Even only this enables SQL Lead() function to find gaps in number sequences. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead() function.
Here is the SQL Analytic LEAD() function syntax for T-SQL developers
LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
The Lead() function input offset argument determines the number of rows in order that the SQL Engine will read from. Offset input parameter is optional. If nothing is provided, then the default value 1 will be used in LEAD() function
The default argument sets the value which will be returned if SQL Lead() function returns nothing.
Here is our first SQL Server Lead() function example.
If you look at the returned result set of the below SQL query, you will realize that all data from AnalyticFunctions table is listed.
Besides the value of the next row when ordered by Id column with the same Category value is displayed in NextValue column.
You see, the Lead() function offset argument is 1 as default value.
This returns the next row value.
And the Lead() function default argument is NULL.
Since the last row for each category does not have a following row, the SQL LEAD() function cannot calculated the NextValue column, and returns the default NULL value.
SELECT
*,
LEAD(Value, 1, NULL) OVER (Partition By Category ORDER BY Id) AS NextValue
FROM AnalyticFunctions
Here is another similar example t-sql code that developers can execute on sample database AdventureWorks2008R2
This SELECT query lists Customer info, Sales Order of the customer and the Total amount. Besides all these information, using new SQL Server Lead() analytic function, the total amount of the next sales to the related customer is also displayed as an additional column.
select
CustomerID, OrderDate, TotalDue,
LEAD(TotalDue, 1, NULL) OVER (Partition By CustomerID ORDER BY OrderDate) NextOrder_TotalDue
from Sales.SalesOrderHeader
As I mentioned before Lead() function can be also used to detect and list gaps in numeric sequences in a SQL Server database table column. Here is a short SQL script where SQL analytic function Lead() is used to find missing numbers in the numeric sequence.
with cte as (
select
OrderId, Lead(OrderId) over (order by OrderId) NextOrder
from Orders
)
select
OrderId, NextOrder
from cte
where OrderId < NextOrder - 1