LAG and LEAD functions
Window functions LAG and LEAD have appeared in SQL Server in version 2012.
These functions return value of expression calculated for previous (LAG) or next (LEAD) row of the result set respectively. Let's consider the example of simple query that deduces codes of printers along with codes of previous and next rows:
Notice that if the next or previous row (in the ascending order of code values) does not exist, NULL value is used. However this behavior can be changed with aid of optional argument (the third one) of each function. Value of this argument will be used in the case when the corresponding row does not exist. In the following example the value of -999 is used when the previous row does note. In the following example, the one row is missed.
To apply the third argument, we have been forced to use the second optional argument with value of 1 which is a default for this argument. This argument defines which row among previous rows (or the next ones) should be used with respect to the current row. In the following example, the second row with respect to current row is used.
Finally note that the order in which the previous or next rows are being picked is given by the ORDER BY clause in OVER clause, but not by the order used for the sorting the query result set. The following query illustrates what was being said.
Let's consider the "classic" solutions for this task to compare these ones with new approach in SQL.