Apply powerful window functions in T-SQL—and increase the performance and speed of your queries
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.
Discover how to:
Go beyond traditional query approaches to express set calculations more efficiently
Delve into ordered set functions such as rank, distribution, and offset
Implement hypothetical set and inverse distribution functions in standard SQL
Use strategies for improving sequencing, paging, filtering, and pivoting
Increase query speed using partitioning, ordering, and coverage indexing
Apply new optimization iterators such as Window Spool
Handle common issues such as running totals, intervals, medians, and gaps
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
Chapter 3 Ordered Set Functions
Hypothetical Set Functions
Inverse Distribution Functions
Chapter 4 Optimization of Window Functions
Improved Parallelism with APPLY
Aggregate and Offset Functions
Chapter 5 T-SQL Solutions Using Window Functions
Virtual Auxiliary Table of Numbers
Sequences of Date and Time Values
Sequences of Keys
TOP N Per Group
Max Concurrent Intervals
Gaps and Islands
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
Itzik Ben-Gan is a principal mentor and a founder of SolidQ. A Microsoft MVP for SQL Server since 1999, Itzik teaches and consults internationally on T-SQL querying, programming, and query tuning. He has written numerous articles for SQL Server Magazine and MSDN, and speaks at industry events such as Microsoft TechEd, DevWeek, PASS, and SQL Server Connections.
Comments about oreilly Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions:
I am always hungry for learning more about T-SQL, and being familiar with ranking functions and the OVER clause as of the 2008 version of the product, I didn't doubt about buying this book, and I do not regret doing so.
The book is well organized, from the introduction to the window functions, all the way till the real life and practical usage. You can learn the basics, the new features introduced with the new version, and you can also trust the author and let him to walk you throughout the wonderland of the query optimizer and how the product implement them at that level. You will learn how to assemble the indexes to get a good performnace, what is in the standard that we do not have in the product yet, how to achieve some of the tasks in versions previous to 2012, and mainly to admire the powerfulness of the window functions, which in my humble opinion relies in their simplicity to solve certain not so simple problems.
Take your time in every chapter, pay attention to the examples, practice, read it again if needed, the end is very rewarding.
I foresee my self using this book as a reference, and I really recommend its reading.
Thank you Itzik, Adam, The Quartet, and all who made this book possible.
Bottom Line Yes, I would recommend this to a friend