Friday, October 7, 2011

Window Functions

Last night I was lucky enough hear Itzik Ben-Gan speak at the SQL Server user group here in London. The topic for the evening was Window Functions, and more specifically the enhancements that have been added to the OVER Clause in Denali.

Window Functions are an incredibly powerful addition to T-SQL, which effectively replace the GROUP BY clause in such a way that different aggregations can be applied to different subsets of the original result set. This is particularly powerful when calculating moving averages, and building cumulative totals. Previously this was difficult and computationally expensive to do in T-SQL.

Here is an article that Itzik wrote for SQL Server Magazine on this topic last year. Certainly worth a read for anyone interested in Window Functions.