Window Functions 2012

Chapter 1: SQL Windowing

• Background of Window Functions
• A Glimpse of Solutions Using Window Functions
• Elements of Window Functions
• Query Elements Supporting Window Functions
• Potential for Additional Filters
• Reuse of Window Definitions

Chapter 2: A Detailed Look at Window Functions

• Window Aggregate Functions
• Ranking Functions
• Distribution Functions
• Offset Functions

Chapter 3 Ordered Set Functions

• Hypothetical Set Functions
• Inverse Distribution Functions
• Offset Functions
• String Concatenation

Chapter 4 Optimization of Window Functions

• Sample Data
• Indexing Guidelines
• Ranking Functions
• Improved Parallelism with APPLY
• Aggregate and Offset Functions
• Distribution Functions

Chapter 5 T-SQL Solutions Using Window Functions

• Virtual Auxiliary Table of Numbers
• Sequences of Date and Time Values
• Sequences of Keys
• Paging
• Removing Duplicates
• Pivoting
• TOP N per Group
• Mode
• Running Totals
• Max Concurrent Intervals
• Packing Intervals
• Gaps and Islands
• Median
• Conditional Aggregate
• Sorting Hierarchies

Window functions, to me, are the most profound feature supported by both standard SQL 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, overcoming a number of shortcomings of the traditional alternatives. The range of problems that window functions help solve is so wide that it is well worth investing your time in learning those. SQL Server 2005 was the version in which window functions were introduced initially. SQL Server 2012 then added more complete support by enhancing some of the existing functions, as well as adding new ones. This book covers both the SQL Server–specific support for window functions, as well as standard SQL’s support, including elements that were not yet implemented in SQL Server.

Who Should Read This Book

This book is intended for SQL Server developers and database administrators (DBAs); those who need to write queries and develop code using T-SQL. The book assumes that you already have at least half a year to a year of experience writing and tuning T-SQL queries.

Organization of This Book

The book covers both the logical aspects of window functions as well as their optimization and practical usage aspects. The logical aspects are covered in the first three chapters. The first chapter explains SQL windowing concepts, the second provides a breakdown of window functions, and the third covers ordered set functions. The fourth chapter covers optimization of window functions in SQL Server 2012. Finally, the fifth and last chapter covers practical uses of window functions.

Chapter 1, “SQL Windowing,” covers 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 distribution functions.

Chapter 3, “Ordered Set Functions,” describes the support standard SQL has for ordered set functions, including hypothetical set functions, inverse distribution functions, and others. The chapter also explains how to achieve similar calculations in SQL Server.

Chapter 4, “Optimization of Window Functions,” covers in detail the optimization of window functions in SQL Server 2012. It provides indexing guidelines for optimal performance, explains how parallelism is handled and how to improve it, discusses the new Window Spool iterator, and more.

Chapter 5, “T-SQL Solutions Using Window Functions,” covers practical uses of window functions to address common business tasks.

Cheers,
Itzik

Chapter 1: Foundations of Querying Microsoft SQL Server 2012 – Online

Chapter 7: Querying Full-Text Data in Microsoft SQL Server 2012 – Online

Chapter 1 & 7 – Download

 

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

Source Code for Window Functions 2012

Window Functions 2012 – Submit Errata

Window Functions 2012 – Submitted Errata

Window Functions 2012 – Old 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/windowfunctions2012/ use URL https://tsql.lucient.com/booksdisplay/windowfunctions2012/