Window Functions 2012

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