Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

by Ben-gan, Itzik

• ISBN13: 9780735658363
• ISBN10: 0735658366
• Format: Paperback
• Copyright: 2012-04-15
• Publisher: Microsoft Press

Summary

Perform efficient database queries with T-SQL window functions Get a detailed look into the practical applications of window functions in Transact-SQL-the database programming language built into Microsoft SQL Server. Led by T-SQL expert Itzik Ben-Gan, you'll learn how to perform calculations against sets of rows in your database-in a flexible, clear, and efficient manner. Delve into SQL windowing concepts and discover practical uses for various T-SQL window functions. Discover how to: Apply SQL windowing concepts and window function design Gain experience with window aggregate, ranking, offset, and distribution functions Know when to use SQL ordered set functions, such as hypothetical set functions and inverse distribution functions Optimize window functions in SQL Server 2012 Use practical examples of T-SQL window functions to address common business tasks

Author Biography

Itzik Ben-Gan, a Microsoft MVP for SQL Server since 1999, is cofounder of SolidQ, a company that provides consulting and training services for the entire Microsoft data platform. He writes numerous articles for SQL Server Pro magazine and speaks at industry events such as the Professional Association for SQL Server (PASS) and Microsoft TechEd.

Table of Contents

Foreword	p. xi
Introduction	p. xiii
SQL Windowing	p. 1
Background of Window Functions	p. 2
Window Functions Described	p. 2
Set-Based vs. Iterative/Cursor Programming	p. 6
Drawbacks of Alternatives to Window Functions	p. 11
A Glimpse of Solutions Using Window Functions	p. 15
Elements of Window Functions	p. 19
Partitioning	p. 20
Ordering	p. 21
Framing	p. 22
Query Elements Supporting Window Functions	p. 23
Logical Query Processing	p. 23
Clauses Supporting Window Functions	p. 25
Circumventing the Limitations	p. 28
Potential for Additional Filters	p. 30
Reuse of Window Definitions	p. 31
Summary	p. 32
A Detailed Look at Window Functions	p. 33
Window Aggregate Functions	p. 33
Window Aggregate Functions Described	p. 33
Supported Windowing Elements	p. 34
Further Filtering Ideas	p. 49
Distinct Aggregates	p. 51
Nested Aggregates	p. 53
Ranking Functions	p. 57
Supported Windowing Elements	p. 58
Row_Number	p. 58
Ntile	p. 63
Rank and Dense_Rank	p. 66
Distribution Functions	p. 68
Supported Windowing Elements	p. 68
Rank Distribution Functions	p. 68
Inverse Distribution Functions	p. 71
Offset Functions	p. 74
Supported Windowing Elements	p. 74
Lag and Lead	p. 74
First_Value, Last_Value, and NTH_Value	p. 76
Summary	p. 79
Ordered Set Functions	p. 81
Hypothetical Set Functions	p. 82
Rank	p. 82
Dense_Rank	p. 84
Percent_Rank	p. 85
Cume_Dist	p. 86
General Solution	p. 87
Inverse Distribution Functions	p. 90
Offset Functions	p. 94
String Concatenation	p. 98
Summary	p. 100
Optimization of Window Functions	p. 101
Sample Data	p. 101
Indexing Guidelines	p. 103
POC Index	p. 104
Backward Scans	p. 105
Columnstore Indexes	p. 108
Ranking Functions	p. 108
Row_Number	p. 109
Ntile	p. 110
Rank and Dense_Rank	p. 111
Improved Parallelism with Apply	p. 112
Aggregate and Offset Functions	p. 116
Without Ordering and Framing	p. 116
With Ordering and Framing	p. 119
Distribution Functions	p. 128
Rank Distribution Functions	p. 128
Inverse Distribution Functions	p. 129
Summary	p. 132
T-SQL Solutions Using Window Functions	p. 133
Virtual Auxiliary Table of Numbers	p. 133
Sequences of Date and Time Values	p. 137
Sequences of Keys	p. 138
Update a Column with Unique Values	p. 138
Applying a Range of Sequence Values	p. 139
Paging	p. 143
Removing Duplicates	p. 145
Pivoting	p. 148
Top N per Group	p. 151
Mode	p. 154
Running Totals	p. 158
Set-Based Solution Using Window Functions	p. 160
Set-Based Solutions Using Subqueries or Joins	p. 161
Cursor-Based Solution	p. 162
CLR-Based Solution	p. 164
Nested Iterations	p. 166
Multirow Update with Variables	p. 167
Performance Benchmark	p. 169
Max Concurrent Intervals	p. 171
Traditional Set-Based Solution	p. 173
Cursor-Based Solution	p. 175
Solutions Based on Window Functions	p. 178
Performance Benchmark	p. 180
Packing Intervals	p. 181
Traditional Set-Based Solution	p. 183
Solutions Based on Window Functions	p. 184
Gaps and Islands	p. 193
Gaps	p. 194
Islands	p. 195
Median	p. 202
Conditional Aggregate	p. 204
Sorting Hierarchies	p. 206
Summary	p. 210
Index	p. 211
Table of Contents provided by Ingram. All Rights Reserved.