Published by Microsoft Press (February 17, 2015) © 2015
Itzik Ben-Gan | Adam Machanic | Dejan Sarka | Kevin FarleeProduct Information
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:
About This Book
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
Foreword xv
Introduction xvii
Chapter 1: Logical query processing 1
Logical query-processing phases 3
Logical query-processing phases in brief 4
Sample query based on customers/orders scenario 6
Logical query-processing phase details 8
Step 1: The FROM phase 8
Step 2: The WHERE phase 14
Step 3: The GROUP BY phase 15
Step 4: The HAVING phase 16
Step 5: The SELECT phase 17
Step 6: The ORDER BY phase 20
Step 7: Apply the TOP or OFFSET-FETCH filter 22
Further aspects of logical query processing 26
Table operators 26
Window functions 35
The UNION, EXCEPT, and INTERSECT operators 38
Conclusion 39
Chapter 2: Query tuning 41
Internals 41
Pages and extents 42
Table organization 43
Tools to measure query performance 53
Access methods 57
Table scan/unordered clustered index scan 57
Unordered covering nonclustered index scan 60
Ordered clustered index scan 62
Ordered covering nonclustered index scan 63
The storage engine’s treatment of scans 65
Nonclustered index seek + range scan + lookups 81
Unordered nonclustered index scan + lookups 91
Clustered index seek + range scan 93
Covering nonclustered index seek + range scan 94
Cardinality estimates 97
Legacy estimator vs. 2014 cardinality estimator 98
Implications of underestimations and overestimations 99
Statistics 101
Estimates for multiple predicates 104
Ascending key problem 107
Unknowns 110
Indexing features 115
Descending indexes 115
Included non-key columns 119
Filtered indexes and statistics 120
Columnstore indexes 123
Inline index definition 130
Prioritizing queries for tuning with extended events 131
Index and query information and statistics 134
Temporary objects 139
Set-based vs. iterative solutions 149
Query tuning with query revisions 153
Parallel query execution 158
How intraquery parallelism works 158
Parallelism and query optimization 175
The parallel APPLY query pattern 181
Conclusion 186
Chapter 3: Multi-table queries 187
Subqueries 187
Self-contained subqueries 187
Correlated subqueries 189
The EXISTS predicate 194
Misbehaving subqueries 201
Table expressions 204
Derived tables 205
CTEs 207
Views 211
Inline table-valued functions 215
Generating numbers 215
The APPLY operator 218
The CROSS APPLY operator 219
The OUTER APPLY operator 221
Implicit APPLY 221
Reuse of column aliases 222
Joins 224
Cross join 224
Inner join 228
Outer join 229
Self join 230
Equi and non-equi joins 230
Multi-join queries 231
Semi and anti semi joins 237
Join algorithms 239
Separating elements 245
The UNION, EXCEPT, and INTERSECT operators 249
The UNION ALL and UNION operators 250
The INTERSECT operator 253
The EXCEPT operator 255
Conclusion 257
Chapter 4: Grouping, pivoting, and windowing 259
Window functions 259
Aggregate window functions 260
Ranking window functions 281
Offset window functions 285
Statistical window functions 288
Gaps and islands 291
Pivoting 299
One-to-one pivot 300
Many-to-one pivot 304
Unpivoting 307
Unpivoting with CROSS JOIN and VALUES 308
Unpivoting with CROSS APPLY and VALUES 310
Using the UNPIVOT operator 312
Custom aggregations 313
Using a cursor 314
Using pivoting 315
Specialized solutions 316
Grouping sets 327
GROUPING SETS subclause 328
CUBE and ROLLUP clauses 331
Grouping sets algebra 333
Materializing grouping sets 334
Sorting 337
Conclusion 339
Chapter 5: TOP and OFFSET-FETCH 341
The TOP and OFFSET-FETCH filters 341
The TOP filter 341
The OFFSET-FETCH filter 345
Optimization of filters demonstrated through paging 346
Optimization of TOP 346
Optimization of OFFSET-FETCH 354
Optimization of ROW_NUMBER 358
Using the TOP option with modifications 360
TOP with modifications 360
Modifying in chunks 361
Top N per group 363
Solution using ROW_NUMBER 364
Solution using TOP and APPLY 365
Solution using concatenation (a carry-along sort) 366
Median 368
Solution using PERCENTILE_CONT 369
Solution using ROW_NUMBER 369
Solution using OFFSET-FETCH and APPLY 370
Conclusion 371
Chapter 6: Data modification 373
Inserting data 373
SELECT INTO 373
Bulk import 376
Measuring the amount of logging 377
BULK rowset provider 378
Sequences 381
Characteristics and inflexibilities of the identity property 381
The sequence object 382
Performance considerations 387
Summarizing the comparison of identity with sequence 394
Deleting data 395
TRUNCATE TABLE 395
Deleting duplicates 399
Updating data 401
Update using table expressions 402
Update using variables 403
Merging data 404
MERGE examples 405
Preventing MERGE conflicts 408
ON isn't a filter 409
USING is similar to FROM 410
The OUTPUT clause 411
Example with INSERT and identity 412
Example for archiving deleted data 413
Example with the MERGE statement 414
Composable DML 417
Conclusion 417
Chapter 7: Working with date and time 419
Date and time data types 419
Date and time functions 422
Challenges working with date and time 434
Literals 434
Identifying weekdays 436
Handling date-only or time-only data with DATETIME and SMALLDATETIME 439
First, last, previous, and next date calculations 440
Search argument 445
Rounding issues 447
Querying date and time data 449
Grouping by the week 449
Intervals 450
Conclusion 471
Chapter 8: T-SQL for BI practitioners 473
Data preparation 473
Sales analysis view 474
Frequencies 476
Frequencies without window functions 476
Frequencies with window functions 477
Descriptive statistics for continuous variables 479
Centers of a distribution 479
Spread of a distribution 482
Higher population moments 487
Linear dependencies 495
Two continuous variables 495
Contingency tables and chi-squared 501
Analysis of variance 505
Definite integration 509
Moving averages and entropy 512
Moving averages 512
Entropy 518
Conclusion 522
Chapter 9: Programmable objects 525
Dynamic SQL 525
Using the EXEC command 525
Using the sp_executesql procedure 529
Dynamic pivot 530
Dynamic search conditions 535
Dynamic sorting 542
User-defined functions 546
Scalar UDFs 546
Multistatement TVFs 550
Stored procedures 553
Compilations, recompilations, and reuse of execution plans 554
Table type and table-valued parameters 571
EXECUTE WITH RESULT SETS 573
Triggers 575
Trigger types and uses 575
Efficient trigger programming 581
SQLCLR programming 585
SQLCLR architecture 586
CLR scalar functions and creating your first assembly 588
Streaming table-valued functions 597
SQLCLR stored procedures and triggers 605
SQLCLR user-defined types 617
SQLCLR user-defined aggregates 628
Transaction and concurrency 632
Transactions described 633
Locks and blocking 636
Lock escalation 641
Delayed durability 643
Isolation levels 645
Deadlocks 657
Error handling 662
The TRY-CATCH construct 662
Errors in transactions 666
Retry logic 669
Conclusion 670
Chapter 10: In-Memory OLTP 671
In-Memory OLTP overview 671
Data is always in memory 672
Native compilation 673
Lock and latch-free architecture 673
SQL Server integration 674
Creating memory-optimized tables 675
Creating indexes in memory-optimized tables 676
Clustered vs. nonclustered indexes 677
Nonclustered indexes 677
Hash indexes 680
Execution environments 690
Query interop 690
Natively compiled procedures 699
Surface-area restrictions 703
Table DDL 703
DML 704
Conclusion 705
Chapter 11: Graphs and recursive queries 707
Terminology 707
Graphs 707
Trees 708
Hierarchies 709
Scenarios 709
Employee organizational chart 709
Bill of materials (BOM) 711
Road system 715
Iteration/recursion 718
Subgraph/descendants 719
Ancestors/path 730
Subgraph/descendants with path enumeration 733
Sorting 736
Cycles 740
Materialized path 742
Maintaining data 743
Querying 749
Materialized path with the HIERARCHYID data type 754
Maintaining data 756
Querying 763
Further aspects of working with HIERARCHYID 767
Nested sets 778
Assigning left and right values 778
Querying 784
Transitive closure 787
Directed acyclic graph 787
Conclusion 801
Index 803
Introduction xvii
Chapter 1: Logical query processing 1
Logical query-processing phases 3
Logical query-processing phases in brief 4
Sample query based on customers/orders scenario 6
Logical query-processing phase details 8
Step 1: The FROM phase 8
Step 2: The WHERE phase 14
Step 3: The GROUP BY phase 15
Step 4: The HAVING phase 16
Step 5: The SELECT phase 17
Step 6: The ORDER BY phase 20
Step 7: Apply the TOP or OFFSET-FETCH filter 22
Further aspects of logical query processing 26
Table operators 26
Window functions 35
The UNION, EXCEPT, and INTERSECT operators 38
Conclusion 39
Chapter 2: Query tuning 41
Internals 41
Pages and extents 42
Table organization 43
Tools to measure query performance 53
Access methods 57
Table scan/unordered clustered index scan 57
Unordered covering nonclustered index scan 60
Ordered clustered index scan 62
Ordered covering nonclustered index scan 63
The storage engine’s treatment of scans 65
Nonclustered index seek + range scan + lookups 81
Unordered nonclustered index scan + lookups 91
Clustered index seek + range scan 93
Covering nonclustered index seek + range scan 94
Cardinality estimates 97
Legacy estimator vs. 2014 cardinality estimator 98
Implications of underestimations and overestimations 99
Statistics 101
Estimates for multiple predicates 104
Ascending key problem 107
Unknowns 110
Indexing features 115
Descending indexes 115
Included non-key columns 119
Filtered indexes and statistics 120
Columnstore indexes 123
Inline index definition 130
Prioritizing queries for tuning with extended events 131
Index and query information and statistics 134
Temporary objects 139
Set-based vs. iterative solutions 149
Query tuning with query revisions 153
Parallel query execution 158
How intraquery parallelism works 158
Parallelism and query optimization 175
The parallel APPLY query pattern 181
Conclusion 186
Chapter 3: Multi-table queries 187
Subqueries 187
Self-contained subqueries 187
Correlated subqueries 189
The EXISTS predicate 194
Misbehaving subqueries 201
Table expressions 204
Derived tables 205
CTEs 207
Views 211
Inline table-valued functions 215
Generating numbers 215
The APPLY operator 218
The CROSS APPLY operator 219
The OUTER APPLY operator 221
Implicit APPLY 221
Reuse of column aliases 222
Joins 224
Cross join 224
Inner join 228
Outer join 229
Self join 230
Equi and non-equi joins 230
Multi-join queries 231
Semi and anti semi joins 237
Join algorithms 239
Separating elements 245
The UNION, EXCEPT, and INTERSECT operators 249
The UNION ALL and UNION operators 250
The INTERSECT operator 253
The EXCEPT operator 255
Conclusion 257
Chapter 4: Grouping, pivoting, and windowing 259
Window functions 259
Aggregate window functions 260
Ranking window functions 281
Offset window functions 285
Statistical window functions 288
Gaps and islands 291
Pivoting 299
One-to-one pivot 300
Many-to-one pivot 304
Unpivoting 307
Unpivoting with CROSS JOIN and VALUES 308
Unpivoting with CROSS APPLY and VALUES 310
Using the UNPIVOT operator 312
Custom aggregations 313
Using a cursor 314
Using pivoting 315
Specialized solutions 316
Grouping sets 327
GROUPING SETS subclause 328
CUBE and ROLLUP clauses 331
Grouping sets algebra 333
Materializing grouping sets 334
Sorting 337
Conclusion 339
Chapter 5: TOP and OFFSET-FETCH 341
The TOP and OFFSET-FETCH filters 341
The TOP filter 341
The OFFSET-FETCH filter 345
Optimization of filters demonstrated through paging 346
Optimization of TOP 346
Optimization of OFFSET-FETCH 354
Optimization of ROW_NUMBER 358
Using the TOP option with modifications 360
TOP with modifications 360
Modifying in chunks 361
Top N per group 363
Solution using ROW_NUMBER 364
Solution using TOP and APPLY 365
Solution using concatenation (a carry-along sort) 366
Median 368
Solution using PERCENTILE_CONT 369
Solution using ROW_NUMBER 369
Solution using OFFSET-FETCH and APPLY 370
Conclusion 371
Chapter 6: Data modification 373
Inserting data 373
SELECT INTO 373
Bulk import 376
Measuring the amount of logging 377
BULK rowset provider 378
Sequences 381
Characteristics and inflexibilities of the identity property 381
The sequence object 382
Performance considerations 387
Summarizing the comparison of identity with sequence 394
Deleting data 395
TRUNCATE TABLE 395
Deleting duplicates 399
Updating data 401
Update using table expressions 402
Update using variables 403
Merging data 404
MERGE examples 405
Preventing MERGE conflicts 408
ON isn't a filter 409
USING is similar to FROM 410
The OUTPUT clause 411
Example with INSERT and identity 412
Example for archiving deleted data 413
Example with the MERGE statement 414
Composable DML 417
Conclusion 417
Chapter 7: Working with date and time 419
Date and time data types 419
Date and time functions 422
Challenges working with date and time 434
Literals 434
Identifying weekdays 436
Handling date-only or time-only data with DATETIME and SMALLDATETIME 439
First, last, previous, and next date calculations 440
Search argument 445
Rounding issues 447
Querying date and time data 449
Grouping by the week 449
Intervals 450
Conclusion 471
Chapter 8: T-SQL for BI practitioners 473
Data preparation 473
Sales analysis view 474
Frequencies 476
Frequencies without window functions 476
Frequencies with window functions 477
Descriptive statistics for continuous variables 479
Centers of a distribution 479
Spread of a distribution 482
Higher population moments 487
Linear dependencies 495
Two continuous variables 495
Contingency tables and chi-squared 501
Analysis of variance 505
Definite integration 509
Moving averages and entropy 512
Moving averages 512
Entropy 518
Conclusion 522
Chapter 9: Programmable objects 525
Dynamic SQL 525
Using the EXEC command 525
Using the sp_executesql procedure 529
Dynamic pivot 530
Dynamic search conditions 535
Dynamic sorting 542
User-defined functions 546
Scalar UDFs 546
Multistatement TVFs 550
Stored procedures 553
Compilations, recompilations, and reuse of execution plans 554
Table type and table-valued parameters 571
EXECUTE WITH RESULT SETS 573
Triggers 575
Trigger types and uses 575
Efficient trigger programming 581
SQLCLR programming 585
SQLCLR architecture 586
CLR scalar functions and creating your first assembly 588
Streaming table-valued functions 597
SQLCLR stored procedures and triggers 605
SQLCLR user-defined types 617
SQLCLR user-defined aggregates 628
Transaction and concurrency 632
Transactions described 633
Locks and blocking 636
Lock escalation 641
Delayed durability 643
Isolation levels 645
Deadlocks 657
Error handling 662
The TRY-CATCH construct 662
Errors in transactions 666
Retry logic 669
Conclusion 670
Chapter 10: In-Memory OLTP 671
In-Memory OLTP overview 671
Data is always in memory 672
Native compilation 673
Lock and latch-free architecture 673
SQL Server integration 674
Creating memory-optimized tables 675
Creating indexes in memory-optimized tables 676
Clustered vs. nonclustered indexes 677
Nonclustered indexes 677
Hash indexes 680
Execution environments 690
Query interop 690
Natively compiled procedures 699
Surface-area restrictions 703
Table DDL 703
DML 704
Conclusion 705
Chapter 11: Graphs and recursive queries 707
Terminology 707
Graphs 707
Trees 708
Hierarchies 709
Scenarios 709
Employee organizational chart 709
Bill of materials (BOM) 711
Road system 715
Iteration/recursion 718
Subgraph/descendants 719
Ancestors/path 730
Subgraph/descendants with path enumeration 733
Sorting 736
Cycles 740
Materialized path 742
Maintaining data 743
Querying 749
Materialized path with the HIERARCHYID data type 754
Maintaining data 756
Querying 763
Further aspects of working with HIERARCHYID 767
Nested sets 778
Assigning left and right values 778
Querying 784
Transitive closure 787
Directed acyclic graph 787
Conclusion 801
Index 803