Itzik Ben-Gan continues a series complexity around NULL:
When using the offset window functions LAG, LEAD, FIRST_VALUE and LAST_VALUE, sometimes you need to control the NULL treatment behavior. By default, these functions return the result of the requested expression in the requested position, irrespective of whether the result of the expression is an actual value or a NULL. However, sometimes you want to continue moving in the relevant direction, (backward for LAG and LAST_VALUE, forward for LEAD and FIRST_VALUE), and return the first non-NULL value if present, and NULL otherwise. The standard gives you control over this behavior using a NULL treatment clause with the following syntax:
offset_function(<expression>) IGNORE_NULLS | RESPECT NULLS OVER(<window specification>)
There are three good examples of functionality around handling NULL which the current implementation of T-SQL is missing.