Foreword......Page 13
Introduction......Page 15
SQL Windowing......Page 21
Window Functions Described......Page 22
Set-Based vs. Iterative/Cursor Programming......Page 26
Drawbacks of Alternatives to Window Functions......Page 31
A Glimpse of Solutions Using Window Functions......Page 35
Elements of Window Functions......Page 39
Partitioning......Page 40
Ordering......Page 41
Framing......Page 42
Logical Query Processing......Page 43
Clauses Supporting Window Functions......Page 45
Circumventing the Limitations......Page 48
Potential for Additional Filters......Page 50
Reuse of Window Definitions......Page 51
Summary......Page 52
Window Aggregate Functions Described......Page 53
Supported Windowing Elements......Page 54
Further Filtering Ideas......Page 69
Distinct Aggregates......Page 71
Nested Aggregates......Page 73
Ranking Functions......Page 77
ROW_NUMBER......Page 78
NTILE......Page 83
RANK and DENSE_RANK......Page 86
Rank Distribution Functions......Page 88
Inverse Distribution Functions......Page 91
LAG and LEAD......Page 94
FIRST_VALUE, LAST_VALUE, and NTH_VALUE......Page 96
Summary......Page 99
Ordered Set Functions......Page 101
RANK......Page 102
DENSE_RANK......Page 104
PERCENT_RANK......Page 105
CUME_DIST......Page 106
General Solution......Page 107
Inverse Distribution Functions......Page 110
Offset Functions......Page 114
String Concatenation......Page 118
Summary......Page 120
Sample Data......Page 121
Indexing Guidelines......Page 123
POC Index......Page 124
Backward Scans......Page 125
Ranking Functions......Page 128
ROW_NUMBER......Page 129
NTILE......Page 130
RANK and DENSE_RANK......Page 131
Improved Parallelism with APPLY......Page 132
Without Ordering and Framing......Page 136
With Ordering and Framing......Page 139
Rank Distribution Functions......Page 148
Inverse Distribution Functions......Page 149
Summary......Page 152
Virtual Auxiliary Table of Numbers......Page 153
Sequences of Date and Time Values......Page 157
Update a Column with Unique Values......Page 158
Applying a Range of Sequence Values......Page 159
Paging......Page 163
Removing Duplicates......Page 165
Pivoting......Page 168
TOP N per Group......Page 171
Mode......Page 174
Running Totals......Page 178
Set-Based Solution Using Window Functions......Page 180
Set-Based Solutions Using Subqueries or Joins......Page 181
Cursor-Based Solution......Page 182
CLR-Based Solution......Page 184
Nested Iterations......Page 186
Multirow UPDATE with Variables......Page 187
Performance Benchmark......Page 189
Max Concurrent Intervals......Page 191
Traditional Set-Based Solution......Page 193
Cursor-Based Solution......Page 195
Solutions Based on Window Functions......Page 198
Performance Benchmark......Page 200
Packing Intervals......Page 201
Traditional Set-Based Solution......Page 203
Solutions Based on Window Functions......Page 204
Gaps and Islands......Page 213
Gaps......Page 214
Islands......Page 215
Median......Page 222
Conditional Aggregate......Page 224
Sorting Hierarchies......Page 226
Summary......Page 230
Index......Page 231