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

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

by Itzik Ben-Gan
Released April 2012
Publisher(s): Microsoft Press
ISBN: 9780735671591

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

    •

    Table of contents

    1. Table of Contents
    2. Dedication
    3. Foreword
    4. Introduction
      1. Who Should Read This Book
      2. Organization of This Book
      3. System Requirements
      4. Code Samples
      5. Acknowledgments
      6. Errata & Book Support
      7. We Want to Hear from You
      8. Stay in Touch
    5. 1. SQL Windowing
      1. Background of Window Functions
        1. Window Functions Described
        2. Set-Based vs. Iterative/Cursor Programming
        3. Drawbacks of Alternatives to Window Functions
      2. A Glimpse of Solutions Using Window Functions
      3. Elements of Window Functions
        1. Partitioning
        2. Ordering
        3. Framing
      4. Query Elements Supporting Window Functions
        1. Logical Query Processing
        2. Clauses Supporting Window Functions
        3. Circumventing the Limitations
      5. Potential for Additional Filters
      6. Reuse of Window Definitions
      7. Summary
    6. 2. A Detailed Look at Window Functions
      1. Window Aggregate Functions
        1. Window Aggregate Functions Described
        2. Supported Windowing Elements
          1. Partitioning
          2. Ordering and Framing
        3. Further Filtering Ideas
        4. Distinct Aggregates
        5. Nested Aggregates
      2. Ranking Functions
        1. Supported Windowing Elements
        2. ROW_NUMBER
          1. Determinism
        3. NTILE
        4. RANK and DENSE_RANK
          1. Determinism
      3. Distribution Functions
        1. Supported Windowing Elements
        2. Rank Distribution Functions
        3. Inverse Distribution Functions
      4. Offset Functions
        1. Supported Windowing Elements
        2. LAG and LEAD
        3. FIRST_VALUE, LAST_VALUE, and NTH_VALUE
      5. Summary
    7. 3. Ordered Set Functions
      1. Hypothetical Set Functions
        1. RANK
        2. DENSE_RANK
        3. PERCENT_RANK
        4. CUME_DIST
        5. General Solution
      2. Inverse Distribution Functions
      3. Offset Functions
      4. String Concatenation
      5. Summary
    8. 4. Optimization of Window Functions
      1. Sample Data
      2. Indexing Guidelines
        1. POC Index
        2. Backward Scans
        3. Columnstore Indexes
      3. Ranking Functions
        1. ROW_NUMBER
        2. NTILE
        3. RANK and DENSE_RANK
      4. Improved Parallelism with APPLY
      5. Aggregate and Offset Functions
        1. Without Ordering and Framing
        2. With Ordering and Framing
          1. UNBOUNDED PRECEDING: The Fast-Track Case
          2. Expanding All Frame Rows
          3. Computing Two Cumulative Values
      6. Distribution Functions
        1. Rank Distribution Functions
        2. Inverse Distribution Functions
      7. Summary
    9. 5. T-SQL Solutions Using Window Functions
      1. Virtual Auxiliary Table of Numbers
      2. Sequences of Date and Time Values
      3. Sequences of Keys
        1. Update a Column with Unique Values
        2. Applying a Range of Sequence Values
      4. Paging
      5. Removing Duplicates
      6. Pivoting
      7. TOP N Per Group
      8. Mode
      9. Running Totals
        1. Set-Based Solution Using Window Functions
        2. Set-Based Solutions Using Subqueries or Joins
        3. Cursor-Based Solution
        4. CLR-Based Solution
        5. Nested Iterations
        6. Multirow UPDATE with Variables
        7. Performance Benchmark
      10. Max Concurrent Intervals
        1. Traditional Set-Based Solution
        2. Cursor-Based Solution
        3. Solutions Based on Window Functions
        4. Performance Benchmark
      11. Packing Intervals
        1. Traditional Set-Based Solution
        2. Solutions Based on Window Functions
      12. Gaps and Islands
        1. Gaps
        2. Islands
      13. Median
      14. Conditional Aggregate
      15. Sorting Hierarchies
      16. Summary
    10. Index
    11. About the Author
    12. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions
    • Author(s): Itzik Ben-Gan
    • Release date: April 2012
    • Publisher(s): Microsoft Press
    • ISBN: 9780735671591