Thursday, January 16, 2014

SQL Server T-SQL Window functions

Windows functions in SQL Server's T-SQL is not related to the Windows operating system but the kind of more detailed ordering (and all the calculations you can do with the ordered set) than you can get by using the regular Group By clause. Windows functions are obviously much more powerful than what you get by mere grouping and sub-queries. There are many enhancements in SQL Server 2012 which are most useful in analytic calculations.

The following windows functions were introduced in SQL Server 2005:

These are enhanced in SQL Server 2012 by providing functions that provide better analytics,

  • Windows Order and Frame clauses
  • Windows Offset functions
  (Lag, Lead, First_Value, Last_value)
  • Windows distribution functions
  (Percent-Rank, Cume_Dist, Percentile_Disc and Percentile_Cont)

Conceptually it is as if you have created a window of rows to which you apply some processing and the result is returned in a single row as related to the windowed rows). You define the window in which the processing takes place by the clause OVER.

Details of a single example using the RANK function can be found here

No comments: Protection Status