Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Released April 2006
Publisher(s): Microsoft Press
ISBN: 9780735623132

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

This thorough, hands-on reference for database developers and administrators delivers expert guidance on sophisticated uses of Transact-SQL (T-SQL)—one of the most familiar and powerful programming languages for SQL Server. Written by a T-SQL guru, this guide focuses on advanced querying techniques and how queries are interpreted and processed by the SQL Server execution engine. You’ll get in-depth coverage of the sophisticated uses of T-SQL, including the differences between logical and physical processing, nesting of queries, and much more. The book explains and compares solutions to database-development problems in both SQL Server 2000 and SQL Server 2005, discussing the new T-SQL programming features added to SQL Server 2005 in detail. Includes extensive code samples, table examples, and logic puzzles to help database developers and administrators understand the intricacies and help promote mastery of T-SQL

Table of contents

  1. Inside Microsoft® SQL Server™ 2005 T-SQL Querying
  2. A Note Regarding Supplemental Files
  3. Foreword
  4. Preface
  5. Acknowledgments
  6. Introduction
    1. Organization of This Book
    2. System Requirements
    3. Installing Sample Databases
    4. Updates
    5. Code Samples
    6. Support for This Book
  7. 1. Logical Query Processing
    1. Logical Query Processing Phases
      1. Brief Description of Logical Query Processing Phases
    2. Sample Query Based on Customers/Orders Scenario
    3. Logical Query Processing Phase Details
      1. Step 1: Performing a Cartesian Product (Cross Join)
      2. Step 2: Applying the ON Filter (Join Condition)
      3. Step 3: Adding Outer Rows
      4. Step 4: Applying the WHERE Filter
      5. Step 5: Grouping
      6. Step 6: Applying the CUBE or ROLLUP Option
      7. Step 7: Applying the HAVING Filter
      8. Step 8: Processing the SELECT List
      9. Step 9: Applying the DISTINCT Clause
      10. Step 10: Applying the ORDER BY Clause
      11. Step 11: Applying the TOP Option
    4. New Logical Processing Phases in SQL Server 2005
      1. Table Operators
        1. APPLY
        2. PIVOT
        3. UNPIVOT
      2. OVER Clause
      3. Set Operations
    5. Conclusion
  8. 2. Physical Query Processing
    1. Flow of Data During Query Processing
    2. Compilation
      1. Algebrizer
        1. Operator Flattening
        2. Name Resolution
        3. Type Derivation
        4. Aggregate Binding
        5. Grouping Binding
      2. Optimization
      3. Working with the Query Plan
        1. SET SHOWPLAN_TEXT and SHOWPLAN_ALL
        2. XML Form of the Showplan
        3. Graphical Showplan
        4. Run-Time Information in Showplan
          1. SET STATISTICS XML ON|OFF
          2. SET STATISTICS PROFILE
        5. Capturing Showplan with SQL Trace
        6. Extracting the Showplan from the Procedure Cache
      4. Update Plans
    3. Conclusion
      1. Acknowledgment
  9. 3. Query Tuning
    1. Sample Data for This Chapter
    2. Tuning Methodology
      1. Analyze Waits at the Instance Level
      2. Correlate Waits with Queues
      3. Determine Course of Action
      4. Drill Down to the Database/File Level
      5. Drill Down to the Process Level
        1. Trace Performance Workload
        2. Analyze Trace Data
      6. Tune Indexes/Queries
    3. Tools for Query Tuning
      1. syscacheobjects
      2. Clearing the Cache
      3. Dynamic Management Objects
      4. STATISTICS IO
      5. Measuring the Run Time of Queries
      6. Analyzing Execution Plans
        1. Graphical Execution Plans
        2. Textual Showplans
        3. XML Showplans
      7. Hints
      8. Traces/Profiler
      9. Database Engine Tuning Advisor
    4. Index Tuning
      1. Table and Index Structures
        1. Pages and Extents
        2. Heap
        3. Clustered Index
        4. Nonclustered Index on a Heap
        5. Nonclustered Index on a Clustered Table
      2. Index Access Methods
        1. Table Scan/Unordered Clustered Index Scan
        2. Unordered Covering Nonclustered Index Scan
        3. Ordered Clustered Index Scan
        4. Ordered Covering Nonclustered Index Scan
        5. Nonclustered Index Seek + Ordered Partial Scan + Lookups
        6. Unordered Nonclustered Index Scan + Lookups
        7. Clustered Index Seek + Ordered Partial Scan
        8. Covering Nonclustered Index Seek + Ordered Partial Scan
        9. Index Intersection
        10. Indexed Views
      3. Index Optimization Scale
        1. Table Scan (Unordered Clustered Index Scan)
        2. Unordered Covering Nonclustered Index Scan
        3. Unordered Nonclustered Index Scan + Lookups
        4. Nonclustered Index Seek + Ordered Partial Scan + Lookups
        5. Clustered Index Seek + Ordered Partial Scan
        6. Covering Nonclustered Index Seek + Ordered Partial Scan
        7. Index Optimization Scale Summary and Analysis
      4. Fragmentation
      5. Partitioning
    5. Preparing Sample Data
      1. Data Preparation
      2. TABLESAMPLE
    6. An Examination of Set-Based vs. Iterative/Procedural Approaches, and a Tuning Exercise
    7. Additional Resources
    8. Conclusion
  10. 4. Subqueries, Table Expressions, and Ranking Functions
    1. Subqueries
      1. Self-Contained Subqueries
      2. Correlated Subqueries
        1. Tiebreaker
        2. EXISTS
          1. EXISTS vs. IN
          2. NOT EXISTS vs. NOT IN
          3. Minimum Missing Value
          4. Reverse Logic Applied to Relational Division Problems
      3. Misbehaving Subqueries
      4. Uncommon Predicates
    2. Table Expressions
      1. Derived Tables
        1. Result Column Aliases
        2. Using Arguments
        3. Nesting
        4. Multiple References
      2. Common Table Expressions (CTE)
        1. Result Column Aliases
        2. Using Arguments
        3. Multiple CTEs
        4. Multiple References
        5. Modifying Data
        6. Container Objects
        7. Recursive CTEs
    3. Analytical Ranking Functions
      1. Row Number
        1. The ROW_NUMBER Function in SQL Server 2005
          1. Determinism
          2. Partitioning
        2. The Set-Based Technique prior to SQL Server 2005
          1. Unique Sort Column
          2. Nonunique Sort Column and Tiebreaker
          3. Nonunique Sort Column without a Tiebreaker
          4. Partitioning
        3. Cursor-Based Solution
        4. IDENTITY-Based Solution
          1. Nonpartitioned
          2. Partitioned
        5. Performance Comparisons
        6. Paging
          1. Ad Hoc Paging
          2. Multipage Access
      2. Rank and Dense Rank
        1. RANK and DENSE_RANK Functions in SQL Server 2005
        2. Set-Based Solutions prior to SQL Server 2005
      3. NTILE
        1. NTILE Function in SQL Server 2005
        2. Other Set-Based Solutions to NTILE
    4. Auxiliary Table of Numbers
    5. Existing and Missing Ranges (Also Known as Islands and Gaps)
      1. Missing Ranges (Also Known as Gaps)
      2. Existing Ranges (Also Known as Islands)
    6. Conclusion
  11. 5. Joins and Set Operations
    1. Joins
      1. Old Style vs. New Style
      2. Fundamental Join Types
        1. CROSS
        2. INNER
        3. OUTER
        4. Nonsupported Join Types
      3. Further Examples of Joins
        1. Self Joins
        2. Nonequijoins
        3. Multiple Joins
          1. Controlling the Physical Join Evaluation Order
          2. Controlling the Logical Join Evaluation Order
        4. Semi Joins
      4. Sliding Total of Previous Year
      5. Join Algorithms
        1. Loop Join
        2. Merge
        3. Hash
        4. Forcing a Join Strategy
      6. Separating Elements
    2. Set Operations
      1. UNION
        1. UNION DISTINCT
        2. UNION ALL
      2. EXCEPT
        1. EXCEPT DISTINCT
        2. EXCEPT ALL
      3. INTERSECT
        1. INTERSECT DISTINCT
        2. INTERSECT ALL
      4. Precedence of Set Operations
      5. Using INTO with Set Operations
      6. Circumventing Unsupported Logical Phases
    3. Conclusion
  12. 6. Aggregating and Pivoting Data
    1. OVER Clause
    2. Tiebreakers
    3. Running Aggregations
      1. Cumulative Aggregations
      2. Sliding Aggregations
      3. Year-To-Date (YTD)
    4. Pivoting
      1. Pivoting Attributes
      2. Relational Division
      3. Aggregating Data
    5. Unpivoting
    6. Custom Aggregations
      1. Custom Aggregations Using Pivoting
        1. String Concatenation Using Pivoting
        2. Aggregate Product Using Pivoting
      2. User Defined Aggregates (UDA)
      3. Specialized Solutions
        1. Specialized Solution for Aggregate String Concatenation
        2. Specialized Solution for Aggregate Product
        3. Specialized Solutions for Aggregate Bitwise Operations
          1. Aggregate Bitwise OR
          2. Aggregate Bitwise AND
          3. Aggregate Bitwise XOR
        4. Median
    7. Histograms
    8. Grouping Factor
    9. CUBE and ROLLUP
      1. CUBE
      2. ROLLUP
    10. Conclusion
  13. 7. TOP and APPLY
    1. SELECT TOP
      1. TOP and Determinism
      2. TOP and Input Expressions
      3. TOP and Modifications
    2. APPLY
    3. Solutions to Common Problems Using TOP and APPLY
      1. TOP n for Each Group
      2. Matching Current and Previous Occurrences
      3. Paging
        1. First Page
        2. Next Page
        3. Previous Page
      4. Random Rows
      5. Median
    4. Conclusion
  14. 8. Data Modification
    1. Inserting Data
      1. SELECT INTO
      2. INSERT EXEC
      3. Inserting New Rows
      4. INSERT with OUTPUT
      5. Sequence Mechanisms
        1. Identity Columns
        2. Custom Sequences
          1. Synchronous Sequence Generation
            1. Single Sequence Value
            2. Block of Sequence Values
          2. Asynchronous Sequence Generation
        3. Globally Unique Identifiers
    2. Deleting Data
      1. TRUNCATE vs. DELETE
      2. Removing Rows with Duplicate Data
      3. DELETE Using Joins
      4. DELETE with OUTPUT
    3. Updating Data
      1. UPDATE Using Joins
      2. UPDATE with OUTPUT
      3. SELECT and UPDATE Statement Assignments
        1. Assignment SELECT
        2. Assignment UPDATE
    4. Other Performance Considerations
    5. Conclusion
  15. 9. Graphs, Trees, Hierarchies, and Recursive Queries
    1. Terminology
      1. Graphs
      2. Trees
      3. Hierarchies
    2. Scenarios
      1. Employee Organizational Chart
      2. Bill of Materials (BOM)
      3. Road System
    3. Iteration/Recursion
      1. Subordinates
      2. Ancestors
      3. Subgraph/Subtree with Path Enumeration
      4. Sorting
      5. Cycles
    4. Materialized Path
      1. Maintaining Data
        1. Adding Employees Who Manage No One (Leaves)
        2. Moving a Subtree
        3. Removing a Subtree
      2. Querying
    5. Nested Sets
      1. Assigning Left and Right Values
      2. Querying
    6. Transitive Closure
      1. Directed Acyclic Graph
        1. Undirected Cyclic Graph
    7. Conclusion
  16. A. Logic Puzzles
    1. Puzzles
      1. Puzzle 1: Medication Tablets
      2. Puzzle 2: Chocolate Bar
      3. Puzzle 3: To a T
      4. Puzzle 4: On the Dot
      5. Puzzle 5: Rectangles in a Square
      6. Puzzle 6: Measuring Time by Burning Ropes
      7. Puzzle 7: Arithmetic Maximum Calculation
      8. Puzzle 8: Covering a Chessboard with Domino Tiles
      9. Puzzle 9: The Missing Buck
      10. Puzzle 10: Flipping Lamp Switches
      11. Puzzle 11: Cutting a Stick to Make a Triangle
      12. Puzzle 12: Rectangle Within a Circle
      13. Puzzle 13: Monty Hall Problem
      14. Puzzle 14: Piece of Cake
      15. Puzzle 15: Cards Facing Up
      16. Puzzle 16: Basic Arithmetic
      17. Puzzle 17: Self-Replicating Code (Quine)
      18. Puzzle 18: Hiking a Mountain
      19. Puzzle 19: Find the Pattern in the Sequence
    2. Puzzle Solutions
      1. Puzzle 1: Medication Tablets
      2. Puzzle 2: Chocolate Bar
      3. Puzzle 3: To a T
      4. Puzzle 4: On the Dot
      5. Puzzle 5: Rectangles in a Square
      6. Puzzle 6: Measuring Time by Burning Ropes
      7. Puzzle 7: Arithmetic Maximum Calculation
      8. Puzzle 8: Covering a Chessboard with Domino Tiles
      9. Puzzle 9: The Missing Buck
      10. Puzzle 10: Alternating Lamp States
      11. Puzzle 11: Cutting a Stick to Make a Triangle
      12. Puzzle 12: Rectangle Within a Circle
      13. Puzzle 13: Monty Hall Problem
      14. Puzzle 14: Piece of Cake
      15. Puzzle 15: Cards Facing Up
      16. Puzzle 16: Basic Arithmetic
      17. Puzzle 17: Self-Replicating Code (Quine)
      18. Puzzle 18: Hiking a Mountain
      19. Puzzle 19: Find the Pattern in the Sequence
    3. Conclusion
  17. About the Contributors
  18. Index
  19. About the Authors
  20. Copyright

Product information

  • Title: Inside Microsoft® SQL Server™ 2005: T-SQL Querying
  • Author(s):
  • Release date: April 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735623132