Microsoft SQL Server 2012 High-Performance
T-SQL Using Window Functions (MSPress)
April 10, 2012
ISBN-10: 0735658366, ISBN-13: 978-0735658363
Order the book at:
Barnes & Noble
- Oreilly (Ebook also
available) (use discount code MSINT,
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.