T-SQL Querying

Itzik Ben-Gan is a mentor for and co-founder of SolidQ. A SQL Server Microsoft MVP (Most Valuable Professional) since 1999, Itzik has delivered numerous training events around the world focused on T-SQL querying, query tuning, and programming. Itzik has authored several T-SQL books as well as articles for SQL Server Pro, SolidQ Journal, and MSDN. Itzik’s speaking activities include TechEd, SQLPASS, SQL Server Connections, SolidQ events, and various user groups around the world. Itzik is the author of SolidQ’s Advanced T-SQL Querying, Programming and Tuning, and T-SQL Fundamentals courses, along with being a primary resource within the company for its T-SQL-related activities. Dejan Sarka, MCT and SQL Server MVP, is an independent consultant, trainer, and developer focusing on database and business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality. On these tough topics, he works and researches together with SolidQ and the Data Quality Institute. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of 11 books about databases and SQL Server, with more to come. He also has developed and is continuing to develop many courses and seminars for SolidQ and Microsoft. He has been a regular speaker at many conferences worldwide for more than 15 years, including Microsoft TechEd, PASS Summit, and others. Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to several books on SQL Server, including T-SQL Querying (Microsoft Press, 2015) and SQL Server 2008 Internals (Microsoft Press, 2009). He regularly speaks at international conferences and training events on a variety of SQL Server topics. Kevin Farlee has over 25 years in the industry, in both database and storage-management software. In his current role as a Storage Engine Program Manager on the Microsoft SQL Server team, he brings these threads together. His current projects include the SQL Server Project “Hekaton” In-Memory OLTP feature.

• 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