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