SQL Server is constantly evolving, and with each new version, new features and enhancements are introduced. In the upcoming version of SQL Server, code-named “Denali”, there are several exciting enhancements to the OVER clause and the functions that can utilize it.

The OVER clause allows you to assign a numerical order to a set of functions within an unordered set of data. It also supports partitions (groupings) of data, in a specified order, and for a specified range of rows within the partition.

In previous versions of SQL Server, the OVER clause was a partial implementation of the ANSI: SQL 2003 standard. It supported the PARTITION BY and ORDER BY clauses for Ranking functions, and only the PARTITION BY clause for Aggregate functions. However, in “Denali”, the PARTITION BY and ORDER BY clauses are now supported for not only Ranking and Aggregate functions, but also for Analytic functions and the new NEXT VALUE FOR function.

One of the major changes in “Denali” is the introduction of the “ROW or RANGE” clause. This clause allows you to specify which rows within the partition are used in evaluating the function. You can specify whether the OVER clause is to use ROWS (for a physical association to the current row) or RANGE (for a logical association to the current row based on the value of the column(s) in the ORDER BY clause). You can also specify the starting and ending points of the window to be used with the function.

Let’s take a look at some examples to understand how these enhancements work. Consider a transaction table with AccountId, Transaction Date, and Transaction Amount columns. In previous versions of SQL Server, if we wanted to calculate the average, count, minimum, maximum, and total of the transaction amounts for each AccountId, we would use the PARTITION BY clause in the OVER clause. However, in “Denali”, we can now use the ORDER BY clause to calculate running aggregates. This means that as we progress to each row, the aggregate function is applied to all of the rows that have been encountered by the partition thus far.

Another use of the OVER clause in “Denali” is for sliding aggregations. This allows us to calculate the aggregate functions for the current row and the two rows preceding it. By using the ROWS clause in the OVER clause, we can specify the range of rows to include in the calculation.

It’s important to note the difference between using the ROWS and RANGE clauses. ROWS is a physical association between rows, while RANGE is a logical association based on the value of the column(s) in the ORDER BY clause. The choice between ROWS and RANGE depends on the specific requirements of your query.

In addition to these enhancements to the OVER clause, “Denali” also introduces 9 new functions. Eight of these are Analytic functions, which require the use of the OVER clause. The ninth function is used for sequence number generation and using the OVER clause with it is optional.

These enhancements to the OVER clause in “Denali” provide more flexibility and power in performing calculations and aggregations on your data. They allow you to easily calculate running aggregates, sliding aggregations, and more. With these new features, SQL Server becomes an even more powerful tool for data analysis and manipulation.

Stay tuned for the release of “Denali” and start exploring these exciting new features in SQL Server!