T-SQL Querying

T-SQL Querying

Released March 2015
Publisher(s): Microsoft Press
ISBN: 9780133986631

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

T-SQL insiders help you tackle your toughest queries and query-tuning problems
Squeeze maximum performance and efficiency from every T-SQL query you write or tune. Four leading experts take an in-depth look at T-SQL’s internal architecture and offer advanced practical techniques for optimizing response time and resource usage. Emphasizing a correct understanding of the language and its foundations, the authors present unique solutions they have spent years developing and refining. All code and techniques are fully updated to reflect new T-SQL enhancements in Microsoft SQL Server 2014 and SQL Server 2012.

Write faster, more efficient T-SQL code:

  • Move from procedural programming to the language of sets and logic

  • Master an efficient top-down tuning methodology

  • Assess algorithmic complexity to predict performance

  • Compare data aggregation techniques, including new grouping sets

  • Efficiently perform data-analysis calculations

  • Make the most of T-SQL’s optimized bulk import tools

  • Avoid date/time pitfalls that lead to buggy, poorly performing code

  • Create optimized BI statistical queries without additional software

  • Use programmable objects to accelerate queries

  • Unlock major performance improvements with In-Memory OLTP

  • Master useful and elegant approaches to manipulating graphs


    • About This Book

  • For experienced T-SQL practitioners

  • Includes coverage updated from Inside Microsoft SQL Server 2008 T-SQL Querying and Inside Microsoft SQL Server 2008 T-SQL Programming

  • Valuable to developers, DBAs, BI professionals, and data scientists

  • Covers many MCSE 70-464 and MCSA/MCSE 70-461 exam topics

    •

    Table of contents

    1. Title Page
    2. Copyright Page
    3. Dedication Page
    4. Contents at a glance
    5. Contents
    6. Foreword
    7. Introduction
      1. Who should read this book
        1. Assumptions
      2. This book might not be for you if...
      3. Organization of this book
      4. System requirements
      5. Downloads: Code samples
      6. Acknowledgments
      7. Errata, updates, & book support
      8. Free ebooks from Microsoft Press
      9. We want to hear from you
      10. Stay in touch
    8. Chapter 1. Logical query processing
      1. Logical query-processing phases
        1. Logical query-processing phases in brief
      2. Sample query based on customers/orders scenario
      3. Logical query-processing phase details
        1. Step 1: The FROM phase
        2. Step 2: The WHERE phase
        3. Step 3: The GROUP BY phase
        4. Step 4: The HAVING phase
        5. Step 5: The SELECT phase
        6. Step 6: The ORDER BY phase
        7. Step 7: Apply the TOP or OFFSET-FETCH filter
      4. Further aspects of logical query processing
        1. Table operators
        2. Window functions
        3. The UNION, EXCEPT, and INTERSECT operators
      5. Conclusion
    9. Chapter 2. Query tuning
      1. Internals
        1. Pages and extents
        2. Table organization
      2. Tools to measure query performance
      3. 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. The storage engine’s treatment of scans
        6. Nonclustered index seek + range scan + lookups
        7. Unordered nonclustered index scan + lookups
        8. Clustered index seek + range scan
        9. Covering nonclustered index seek + range scan
      4. Cardinality estimates
        1. Legacy estimator vs. 2014 cardinality estimator
        2. Implications of underestimations and overestimations
        3. Statistics
        4. Estimates for multiple predicates
        5. Ascending key problem
        6. Unknowns
      5. Indexing features
        1. Descending indexes
        2. Included non-key columns
        3. Filtered indexes and statistics
        4. Columnstore indexes
        5. Inline index definition
      6. Prioritizing queries for tuning with extended events
      7. Index and query information and statistics
      8. Temporary objects
      9. Set-based vs. iterative solutions
      10. Query tuning with query revisions
      11. Parallel query execution
        1. How intraquery parallelism works
        2. Parallelism and query optimization
        3. The parallel APPLY query pattern
      12. Conclusion
    10. Chapter 3. Multi-table queries
      1. Subqueries
        1. Self-contained subqueries
        2. Correlated subqueries
        3. The EXISTS predicate
        4. Misbehaving subqueries
      2. Table expressions
        1. Derived tables
        2. CTEs
        3. Views
        4. Inline table-valued functions
        5. Generating numbers
      3. The APPLY operator
        1. The CROSS APPLY operator
        2. The OUTER APPLY operator
        3. Implicit APPLY
        4. Reuse of column aliases
      4. Joins
        1. Cross join
        2. Inner join
        3. Outer join
        4. Self join
        5. Equi and non-equi joins
        6. Multi-join queries
        7. Semi and anti semi joins
        8. Join algorithms
        9. Separating elements
      5. The UNION, EXCEPT, and INTERSECT operators
        1. The UNION ALL and UNION operators
        2. The INTERSECT operator
        3. The EXCEPT operator
      6. Conclusion
    11. Chapter 4. Grouping, pivoting, and windowing
      1. Window functions
        1. Aggregate window functions
        2. Ranking window functions
        3. Offset window functions
        4. Statistical window functions
        5. Gaps and islands
      2. Pivoting
        1. One-to-one pivot
        2. Many-to-one pivot
      3. Unpivoting
        1. Unpivoting with CROSS JOIN and VALUES
        2. Unpivoting with CROSS APPLY and VALUES
        3. Using the UNPIVOT operator
      4. Custom aggregations
        1. Using a cursor
        2. Using pivoting
        3. Specialized solutions
      5. Grouping sets
        1. GROUPING SETS subclause
        2. CUBE and ROLLUP clauses
        3. Grouping sets algebra
        4. Materializing grouping sets
        5. Sorting
      6. Conclusion
    12. Chapter 5. TOP and OFFSET-FETCH
      1. The TOP and OFFSET-FETCH filters
        1. The TOP filter
        2. The OFFSET-FETCH filter
      2. Optimization of filters demonstrated through paging
        1. Optimization of TOP
        2. Optimization of OFFSET-FETCH
        3. Optimization of ROW_NUMBER
      3. Using the TOP option with modifications
        1. TOP with modifications
        2. Modifying in chunks
      4. Top N per group
        1. Solution using ROW_NUMBER
        2. Solution using TOP and APPLY
        3. Solution using concatenation (a carry-along sort)
      5. Median
        1. Solution using PERCENTILE_CONT
        2. Solution using ROW_NUMBER
        3. Solution using OFFSET-FETCH and APPLY
      6. Conclusion
    13. Chapter 6. Data modification
      1. Inserting data
        1. SELECT INTO
        2. Bulk import
        3. Measuring the amount of logging
        4. BULK rowset provider
      2. Sequences
        1. Characteristics and inflexibilities of the identity property
        2. The sequence object
        3. Performance considerations
        4. Summarizing the comparison of identity with sequence
      3. Deleting data
        1. TRUNCATE TABLE
        2. Deleting duplicates
      4. Updating data
        1. Update using table expressions
        2. Update using variables
      5. Merging data
        1. MERGE examples
        2. Preventing MERGE conflicts
        3. ON isn’t a filter
        4. USING is similar to FROM
      6. The OUTPUT clause
        1. Example with INSERT and identity
        2. Example for archiving deleted data
        3. Example with the MERGE statement
        4. Composable DML
      7. Conclusion
    14. Chapter 7. Working with date and time
      1. Date and time data types
      2. Date and time functions
      3. Challenges working with date and time
        1. Literals
        2. Identifying weekdays
        3. Handling date-only or time-only data with DATETIME and SMALLDATETIME
        4. First, last, previous, and next date calculations
        5. Search argument
        6. Rounding issues
      4. Querying date and time data
        1. Grouping by the week
        2. Intervals
      5. Conclusion
    15. Chapter 8. T-SQL for BI practitioners
      1. Data preparation
        1. Sales analysis view
      2. Frequencies
        1. Frequencies without window functions
        2. Frequencies with window functions
      3. Descriptive statistics for continuous variables
        1. Centers of a distribution
        2. Spread of a distribution
        3. Higher population moments
      4. Linear dependencies
        1. Two continuous variables
        2. Contingency tables and chi-squared
        3. Analysis of variance
        4. Definite integration
      5. Moving averages and entropy
        1. Moving averages
        2. Entropy
      6. Conclusion
    16. Chapter 9. Programmable objects
      1. Dynamic SQL
        1. Using the EXEC command
        2. Using the sp_executesql procedure
        3. Dynamic pivot
        4. Dynamic search conditions
        5. Dynamic sorting
      2. User-defined functions
        1. Scalar UDFs
        2. Multistatement TVFs
      3. Stored procedures
        1. Compilations, recompilations, and reuse of execution plans
        2. Table type and table-valued parameters
        3. EXECUTE WITH RESULT SETS
      4. Triggers
        1. Trigger types and uses
        2. Efficient trigger programming
      5. SQLCLR programming
        1. SQLCLR architecture
        2. CLR scalar functions and creating your first assembly
        3. Streaming table-valued functions
        4. SQLCLR stored procedures and triggers
        5. SQLCLR user-defined types
        6. SQLCLR user-defined aggregates
      6. Transaction and concurrency
        1. Transactions described
        2. Locks and blocking
        3. Lock escalation
        4. Delayed durability
        5. Isolation levels
        6. Deadlocks
      7. Error handling
        1. The TRY-CATCH construct
        2. Errors in transactions
        3. Retry logic
      8. Conclusion
    17. Chapter 10. In-Memory OLTP
      1. In-Memory OLTP overview
        1. Data is always in memory
        2. Native compilation
        3. Lock and latch-free architecture
        4. SQL Server integration
      2. Creating memory-optimized tables
      3. Creating indexes in memory-optimized tables
        1. Clustered vs. nonclustered indexes
        2. Nonclustered indexes
        3. Hash indexes
      4. Execution environments
        1. Query interop
        2. Natively compiled procedures
      5. Surface-area restrictions
        1. Table DDL
        2. DML
      6. Conclusion
    18. Chapter 11. Graphs 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. Subgraph/descendants
        2. Ancestors/path
        3. Subgraph/descendants with path enumeration
        4. Sorting
        5. Cycles
      4. Materialized path
        1. Maintaining data
        2. Querying
      5. Materialized path with the HIERARCHYID data type
        1. Maintaining data
        2. Querying
        3. Further aspects of working with HIERARCHYID
      6. Nested sets
        1. Assigning left and right values
        2. Querying
      7. Transitive closure
        1. Directed acyclic graph
      8. Conclusion
    19. Index
    20. Code Snippets

    Product information

    • Title: T-SQL Querying
    • Author(s):
    • Release date: March 2015
    • Publisher(s): Microsoft Press
    • ISBN: 9780133986631