Microsoft introduced the first window (aka, windowing or windowed) functions with SQL Server 2005. These functions were
NTILE,and the window aggregates. Many folks, including myself, used these functions without realizing they were part of a special group. In 2012, Microsoft added several more:
PERCENTILE_DISC. They also added the ability to do running totals and moving calculations.
These functions were promoted as improving performance over older techniques, but that isn’t always the case. There were still performance problems with the aggregate functions introduced in 2005 and the four of the functions introduced in 2012. In 2019, Microsoft introduced Batch Mode on Row Store, available on Enterprise and Developer Editions, that can improve the performance of window aggregates and the four statistical functions from 2012.
I started writing this article to compare some window function solutions to traditional solutions. I found that there were so many ways to write a query that includes a column from another row that this article is dedicated to the window functions
In these sorts of circumstances,
LAG() is extremely efficient at its job. Click through to see just how efficient.