New Lead Function in SQL Server 2012

In this article, I would like to show the new built-in function Lead in SQL Server. The Lead Function returns simply the next Nth row value in an order or accesses data from a subsequent row. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead function. So let's have a look at a practical example of how to use a Lead statement in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

We'll start by walking through a simple LEAD function.

Creating a table in SQL Server

Now we create a table named employee.

Create table Employee


EmpID int,

EmpName varchar(30),

EmpSalary int



The following is the sample data for the employee table.




Lead Function


The Lead function is an important function for accessing data from a subsequent row in SQL Server. The Lead function accesses data from a subsequent row in the result by the order selected in the OVER clause for each partition specified in the OVER clause.



The following is the SQL Analytic Lead function syntax:

LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)

Offset: An input parameter is optional. The offset argument determines the number of rows in order that the SQL Engine will read from. If nothing is provided, then the default value 1 will be used in the Lead function.
OVER: Specifies the order of the rows.
ORDER BY: Provide sort order for the records.
Partition By: The Partition by clause is an optional part of the Lead function and if you don't use it all the records of the result-set will be considered as a part of a single record group or a single partition and then ranking functions are applied.


Let us see the following query:

Select *, Lead(EmpName) OVER (ORDER BY EmpName DESC) AS Result  From Employee



Lead Function with Partition By Clause

The Partition by clause is an optional part of the Lead function. The PARTITION BY clause is specified to partition the rows in the result set. The LEAD function is applied to each partition separately and computation restarts for each partition.


Select *, Lead(EmpName) OVER (partition by EmpName ORDER BY EmpName DESC) AS Result  From Employee





Lead Function with Group By Clause


Select Lead(EmpName) OVER ( ORDER BY EmpName DESC) AS EmployeeName, SUM(EmpSalary) as EmpSalary  From Employee GROUP BY EmpName

HAVING SUM(EmpSalary) < 30000




Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now