New book: Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
We’re happy to announce the availability of Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions (ISBN: 9780735658363; 244 pages), by Itzik Ben-Gan!
As Itzik explains, “Window functions are functions applied to sets of rows defined by a clause called OVER. They are used mainly for analytical purposes allowing you to calculate running totals, calculate moving averages, identify gaps and islands in your data, and perform many other computations. These functions are based on an amazingly profound concept in standard SQL (which is both an ISO and ANSI standard)—the concept of windowing. The idea behind this concept is to allow you to apply various calculations to a set, or window, of rows and return a single value. Window functions can help to solve a wide variety of querying tasks by helping you express set calculations more easily, intuitively, and efficiently than ever before.”
Whether you’re a developer or DBA, this book explains how to apply window functions to increase the speed and effectiveness of your queries and optimize common business tasks.
For a preview, here are the book’s Contents at a Glance, Foreword, and an excerpt from the Introduction. You can get a sampler of the book, which includes the entire first chapter, here:
https://www.microsoftpressstore.com/content/images/9780735658363/samplepages/9780735658363.pdf
Contents at a Glance
Chapter 1 SQL Windowing 1
Chapter 2 A Detailed Look at Window Functions 33
Chapter 3 Ordered Set Functions 81
Chapter 4 Optimization of Window Functions 101
Chapter 5 SQL Solutions Using Window Functions 133
Foreword
SQL is a very interesting programming language. When meeting with customers, I am constantly reminded of the language’s dual nature with regard to complexity. Many people getting started with SQL see it as a simple programming language that supports four basic verbs: SELECT, INSERT, UPDATE, and DELETE. Some people never get much further than this. Maybe a few more figure out how to filter rows in a query using the WHERE clause and perhaps do the occasional JOIN. However, those who spend more time with SQL and learn about its declarative, relational, and set-based model will find a rich programming language that keeps you coming back for more.
One of the most fundamental additions to the SQL language, back in Microsoft SQL Server 2005, was the introduction of window functions with syntactic constructs such as the OVER clause and a new set of functions known as ranking functions (ROW_NUMBER, RANK, and so on). This addition enabled solving common problems in an easier, more intuitive, and often better-performing way than what was previously possible. A few years later, the single most-requested language feature was for Microsoft to extend its support for window functions—with a set of new functions and, more importantly, with the concept of frames. As a result of these requests from a wide range of customers, Microsoft decided to continue investing in window functions extensions in SQL Server 2012.
Today, when I talk to customers about new language functionality in SQL Server 2012, I always recommend they spend extra time with the new window functions and really understand the new dimension that this brings to the SQL language. I am happy that you are reading this book and thus taking what I am sure is precious time to learn how to use this rich functionality. I am confident that the combination of using SQL Server 2012 and reading this book will help you become an even more efficient SQL Server user, and help you solve both simple as well as complex problems significantly faster than before.
Enjoy!
Tobias Ternström
Lead Program Manager,
Microsoft SQL Server Engine team
Introduction
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.
System Requirements
Window functions are part of the core database engine of Microsoft SQL Server 2012; hence, all editions of the product support this feature. To run the code samples in this book, you need access to an instance of the SQL Server 2012 database engine (any edition), and you need to have the sample database installed. If you don’t have access to an existing instance, Microsoft provides trial versions. You can find details at: https://www.microsoft.com/sql. For hardware and software requirements, please consult SQL Server Books Online at: https://msdn.microsoft.com/en-us/library/
Code Samples
This book features a companion website that makes available to you all the code used in the book, sample data, the errata, additional resources, and more, at the following page:
In this website, go to the Books section and select the main page for the book in question. The book’s page has a link to download a compressed file with the book’s source code, including a file called TSQL2012.sql that creates and populates the book’s sample database, TSQL2012.