This article gives an overview of the SQL Lag function and its comparison with the SQL Lead function.

It is a window function available from SQL Server 2012 onwards.

In the lead function, we access subsequent rows, but in lag function, we access previous rows.

It is a useful function in comparing the current row value from the previous row value.

It is a mandatory argument, and we cannot execute the lag function without this.

The lag function uses this argument forgo behind the number of rows (offset).

If we do not specify any value for this, the lag function displays NULL in the output for out of range values.

Suppose we have an extensive data set and we require calculations on a smaller set of data, we can define partitions for it.

For example, sales data for an organization might contain data for several years.

If we use incompatible data types, we get the following error message.

In the output, you can see we have a default value for row 1 and 2.

You can refer to the SQL Server Lead function to create this table.

We use the PARTITION BY clause on the Year column and define the logical subset of data on a yearly basis.

We use the Order by clause on year and quarter columns to sort data first on a yearly basis and then monthly.

The Lag function individually works on each partition and calculates the required data.

He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWSAmazon RDS, Git, and related technologies that have been viewed by over 10m readers to date.

Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious Best author of the year continuously in 2020 and 2021 at SQLShack.

Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16gmail.com View all posts by Rajendra Gupta Latest posts by Rajendra Gupta ( see all ) SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries - March 1, 2021 Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021 Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021.

T Sql Lag Lead