Kathi Kellenberger - Expert T-SQL Window Functions in SQL Server 2019: The Hidden Secret to Fast Analytic and Reporting Queries

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484251966 . For more detailed information, please visit http://www.apress.com/source-code .

SQL was developed in the 1970s and became standardized through ANSI-approved committees as a formal standard starting in 1986. Today in 2019, SQL has become the most widely used declarative language. Along the way, window functions have come to be an important part of that standard. ANSI does not make standards but plays an important role in documenting and preserving them. The individual software vendors voluntarily decide to comply, and its the work of the authors of books like this one to explain SQL use in practical terms.

In my own career in data science and advanced analytics, window functions have been an important part of several key projects in the past few years. Several years ago, I made a YouTube video for a user group based on the earlier edition of this book. Since then, as a career architect at Microsoft, I have advised the application for data science. In one project, the input of about 20 features was not yielding adequate results: using window functions, a team under my leadership (and yes, direct coding) quickly grew that number to over 1,000. More than numeric growth, the accuracy rates improved, and on the business story, the organization is saving millions of dollars annually for their question. In the past month, I have encountered an unrelated new project, and a similar story is there: a time-series type of data set and an opportunity to grow from under 20 features to a number much larger.

One wonders whether automated machine learning technologies would make such combinations on their own, and Im skeptical. Making a robust set of features from window functions requires not just time-series considerations but also clustering knowledge based on knowing the data domain. Even if automated technologies make great progress in this topic, I anticipate the need for any data scientist to have simple knowledge of these functions for the more typical data science investigation which has only a few features and low number of observations.

SQL is central to on-premise and cloud database technologies and in the data science world, many use Apache Spark (part of SQL Server 2019 and so many other data technologies). This reach into advanced analytics is yet another reason why this topic is an expert-level subject in the SQL language. The mainstream applications extend from any business analytics SQL query and even into supporting advanced analytics and machine learning algorithms.

Several years ago, I would create a user group presentation for each new version of SQL Server about the new T-SQL features. There was so much to say in 2012 that I decided to build a presentation on just the windowing functions introduced that year. Eventually, I had so much material that it turned into two sessions. Over the years, I have probably presented this information at least 50 times at events around the United States and the United Kingdom. Despite that, most people still are not using windowing functions because they havent heard about them or do not realize the benefits.

This book covers each type of windowing function beginning with the ranking functions introduced with SQL Server 2005 through the statistical functions introduced in 2012. Each chapter explains how to use the functions along with any options and provides a few simple examples of how to use them. Unfortunately, the last time that Microsoft added any new windowing functions was 2012, but there have been some performance improvements more recently. One chapter is dedicated to the performance of windowing functions.

Finally, the last two chapters in the book cover some real-world examples. In Chapter show how windowing functions can be used in data warehouse calculations instead of building a cube.