T-SQL insiders help you tackle your toughest queries and query-tuning problemsSqueeze 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:

Foreword xvIntroduction xviiChapter 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