SQL Windowing

Window functions can help solve a wide variety of querying tasks by helping you express set calculations more easily, intuitively, and efficiently than ever before. Window functions are functions applied to sets of rows defined by a clause called OVER. They are used mainly for analytical purposes, allowing you to calculate running totals, calculate moving averages, identify gaps and islands in your data, handle intervals, and perform many other computations. These functions are based on an amazingly profound concept in the ISO/IEC SQL standard—the concept of windowing. The idea behind windowing is to allow you to apply various calculations to a set, or window, of rows and return a single value.

Window functions have evolved quite substantially in SQL Server and Azure SQL Database since their inception in SQL Server 2005. I’ll describe their evolution shortly. There’s still some standard functionality missing, but with the enhancements added over the years, the support is quite extensive. In this book, I cover both the functionality SQL Server implements as well as standard functionality that is still missing. When I describe a feature for the first time in the book, I also mention whether it is supported in SQL Server.

From the time SQL Server introduced support for window functions, I found myself using those functions more frequently to improve my solutions. I keep replacing older solutions that rely on more classic, traditional language constructs with the newer window functions. And the results I’m getting are usually simpler and more efficient. This happens to such an extent that the majority of my querying solutions nowadays make use of window functions. Also, the SQL standard, relational database management systems (RDBMSs), and the data industry in general, are moving toward analytical solutions, and window functions are an important part of this trend. The online transactional processing (OLTP) focus of the 90s is gone. Therefore, I think window functions are the future in terms of SQL querying solutions; the time you take to learn them is time well spent.

This book provides extensive coverage of window functions, their optimization, and querying solutions implementing them. This chapter starts by explaining the concept, and it provides

• A background of window functions

• A glimpse of solutions using windows functions

• Coverage of the elements involved in window specifications

• An account of the query elements supporting window functions

• A description of the standard’s solution for reusing window definitions

Evolution of Window Functions

As mentioned, window functions evolved quite extensively since their inception in SQL Server, and there’s still potential for further enhancements in the future. Figure 1-1 illustrates the major milestones when SQL Server introduced features related to window functions, including unsupported major features that hopefully will be added in the future.

FIGURE 1-1 Evolution of window functions.

At this stage of the book, some of these features may not mean much to you yet. This timeline is provided mainly for reference purposes. Rest assured that by the time you’re done reading the book, they will all be meaningful to you. Here’s a brief description of the milestones from Figure 1-1:

• SQL Server 2005 introduced support for window aggregate functions—but without a window frame yet—as well as full support for window ranking functions (ROW_NUMBER, RANK, DENSE_RANK, and NTILE).

• SQL Server 2008 and 2008 R2 did not introduce windowing capabilities.

• SQL Server 2012 introduced support for window aggregate functions with a frame, window offset functions (LAG, LEAD, FIRST_VALUE, and LAST_VALUE), and window statistical functions (PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, and PERCENTILE_DISC).

• SQL Server 2014 did not introduce windowing capabilities.

• SQL Server 2016 introduced optimization improvements for window functions with an entirely new batch-mode Window Aggregate operator. However, in order to benefit from this improvement, there has to be a columnstore index present on at least one of the tables participating in the query.

• SQL Server 2017 introduced support for the first ordered set function STRING_AGG, which applies string concatenation as an aggregate grouped function.

• SQL Server 2019 introduces support for batch mode on rowstore, enabling the use of batch mode operators on rowstore data, and lifting the previous requirement to have a columnstore index present on the queried data.

• A few major windowing capabilities are not yet supported in SQL Server at the date of this writing. Those include the RANGE window frame unit with an INTERVAL type, row pattern recognition, nested window functions, the ability to indicate the RESPECT | IGNORE NULLS option, the WINDOW clause to reuse window definitions, more ordered set functions, and others.