T-SQL Window Functions Second Edition

Chapter 01: SQL Windowing

Chapter 02: A Detailed Look at Window Functions

Chapter 03: Ordered Set Functions

Chapter 04: Row Pattern Recognition in SQL

Chapter 05: Optimization of Window Functions

Chapter 06: T-SQL Solutions using Window Functions

To me, Window functions are the most profound feature supported by both the SQL standard and Microsoft SQL Server’s dialect—T-SQL. They allow you to perform calculations against sets of rows in a flexible, clear, and efficient manner. The design of window functions is ingenious and overcomes a number of shortcomings of the traditional alternatives. The range of tasks that window functions help solve is so wide that it is well worth investing your time in learning those. Window functions have evolved quite substantially since their inception in SQL Server over the different versions, as well as in the SQL standard. This book covers both the SQL Server–specific support for window functions as well as the SQL standard’s support, including elements that were not yet implemented in SQL Server.
This book is intended for SQL Server developers, database administrators (DBAs), data scientists, business intelligence (BI) specialists, and those who need to write queries and develop code using T-SQL. The book assumes that you already have at least six months to a year of experience writing and tuning T-SQL queries.
The book covers both the logical aspects of window functions as well as their optimization and practical usage aspects.
Chapter 1, “SQL Windowing,” explains the standard SQL windowing concepts. It describes the design of window functions, the types of window functions, and the
elements involved in a window specification, such as partitioning, ordering, and framing.
Chapter 2, “A Detailed Look at Window Functions,” gets into the details and specifics of the different window functions. It describes window aggregate functions, window ranking functions, window offset functions, and window statistical (distribution) functions.
Chapter 3, “Ordered Set Functions,” describes the support that T-SQL and the SQL standard have for ordered set functions, including string concatenation, hypothetical set functions, inverse distribution functions, and others. For standard functions that are not yet available in T-SQL, the chapter provides working solutions.
Chapter 4, “Row-Pattern Recognition in SQL,” describes a profound standard concept for data analysis called row-pattern recognition (RPR) that you could think of as the next step in the evolution of window functions. This concept is not available yet in T-SQL, but as mentioned, this book does cover important standard analytical features even if not available yet in T-SQL.
Chapter 5, “Optimization of Window Functions,” covers in detail the optimization of window functions in SQL Server and Azure SQL Database. It provides indexing guidelines for optimal performance, explains how parallelism is handled and how to improve it, row-mode processing versus batch mode processing, and more.
Chapter 6, “T-SQL Solutions Using Window Functions,” covers practical uses of window functions to address common business tasks.
Compared to the first edition of this book entitled “Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions,” this edition entitled “T-SQL Window Functions: For data analysis and beyond, 2nd Edition,” adds:
– An entirely new chapter on Row Pattern Recognition (RPR)
– Coverage of more standard features
– Coverage of batch processing and batch mode on rowstore
– More solutions using window functions

Itzik

Copyright notice: you’re only authorized to download the source code if you own a legal copy of the book.

Source code for T-SQL Window Functions 2nd Edition

T-SQL Window Functions – Submit Errata

Update regarding URLs referenced in the book:
Wherever you encounter a URL pointing to the website tsql.solidq.com, please replace solidq with lucient. For example, instead of URL https://tsql.solidq.com/booksdisplay/wf2/ use URL https://tsql.lucient.com/booksdisplay/wf2/