Transcription
T-SQL QueryingItzik Ben-GanDejan SarkaAdam MachanicKevin Farlee
PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright 2015 by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee. All rights reserved.No part of the contents of this book may be reproduced or transmitted in any form or by any means withoutthe written permission of the publisher.Library of Congress Control Number: 2014951866ISBN: 978-0-7356-8504-8Printed and bound in the United States of America.First PrintingMicrosoft Press books are available through booksellers and distributors worldwide. If you need support relatedto this book, email Microsoft Press Support at mspinput@microsoft.com. Please tell us what you think of thisbook at http://aka.ms/tellpress.This book is provided “as-is” and expresses the authors’ views and opinions. The views, opinions, and informationexpressed in this book, including URL and other Internet website references, may change without notice.Some examples depicted herein are provided for illustration only and are fictitious. No real association orconnection is intended or should be inferred.Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are trademarksof the Microsoft group of companies. All other marks are the property of their respective owners.Acquisitions Editor: Devon MusgraveDevelopmental Editor: Devon MusgraveProject Editor: Carol DillinghamEditorial Production: Curtis Philips, Publishing.comTechnical Reviewer: Alejandro Mesa; Technical Review services provided byContent Master, a member of CM Group, Ltd.Copyeditor: Roger LeBlancProofreader: Andrea FoxIndexer: William P. MeyersCover: Twist Creative Seattle and Joel Panchot
To Lilach, for giving meaning to everything that I do.—Itzik
Contents at a glanceForewordIntroductionxvxviiCHAPTER 1Logical query processing1CHAPTER 2Query tuningCHAPTER 3Multi-table queries187CHAPTER 4Grouping, pivoting, and windowing259CHAPTER 5TOP and OFFSET-FETCH341CHAPTER 6Data modification373CHAPTER 7Working with date and time419CHAPTER 8T-SQL for BI practitioners473CHAPTER 9Programmable objects525CHAPTER 10In-Memory OLTP671CHAPTER 11Graphs and recursive queries707Index80341
ContentsForeword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xviiChapter 1Logical query processing1Logical 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Chapter 2Query tuning41Internals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Pages and extents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Table organization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Tools to measure query performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53What do you think of this book? We want to hear from you!Microsoft is interested in hearing your feedback so we can continually improve our books and learningresources for you. To participate in a brief online survey, please visit:microsoft.com/learning/booksurveyv
Access 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186viContents
Chapter 3Multi-table queries187Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257Contentsvii
Chapter 4Grouping, pivoting, and windowing259Window 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339Chapter 5TOP and OFFSET-FETCH341The TOP and OFFSET-FETCH filters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341The TOP filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341The OFFSET-FETCH filter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345Optimization of filters demonstrated through paging . . . . . . . . . . . . . . . 346Optimization of TOP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346viiiContents
Optimization 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371Chapter 6Data modification373Inserting 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403Contentsix
Merging 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417Chapter 7Working with date and time419Date 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 andSMALLDATETIME. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439First, last, previous, and next date calculations . . . . . . . . . . . . . . . . 440Search argument . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445Rounding issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447Querying date and time data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449Grouping by the week. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449Intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471Chapter 8T-SQL for BI practitioners473Data preparation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473Sales analysis view. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474Frequencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476Frequencies without window functions. . . . . . . . . . . . . . . . . . . . . . . 476xContents
Frequencies 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522Chapter 9Programmable objects525Dynamic 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581Contentsxi
SQLCLR 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670Chapter 10 In-Memory OLTP671In-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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680xiiContents
Execution environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 690Query interop. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 690Natively compiled procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699Surface-area restrictions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703Table DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704Conclusion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705Chapter 11 Graphs and recursive queries707Terminology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 784Contentsxiii
Transitive closure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787Directed acyclic graph. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787Conclusion. . . . . . . . . . . . . . . . . . . . . . . .
Contents at a glance Foreword xv Introduction xvii CHAPTER 1 Logical query processing 1 CHAPTER 2 Query tuning 41 CHAPTER 3 Multi-table queries 187 CHAPTER 4 Grouping, pivoting, and windowing 259 CHAPTER 5 TOP and OFFSET-FETCH 341 CHAPTER 6 Data modification 373 CHAPTER 7 Working with da
