Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference)

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

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

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.