T-SQL Querying - pearsoncmg.com

1 T-SQL QueryingItzik Ben-GanDejan SarkaAdam MachanicKevin FarleePUBLISHED BYMicrosoft PressA Division of microsoft CorporationOne microsoft WayRedmond, Washington 98052-6399 Copyright 2015 by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee. All rights part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the of Congress Control Number: 2014951866 ISBN: 978-0-7356-8504-8 Printed and bound in the United States of PrintingMicrosoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email microsoft Press Support at Please tell us what you think of this book at book is provided as-is and expresses the authors views and opinions.

2 The views, opinions, and information expressed in this book, including URL and other Internet website references, may change without examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be and the trademarks listed at on the Trademarks webpage are trademarks of the microsoft group of companies. All other marks are the property of their respective Editor: Devon MusgraveDevelopmental Editor: Devon Musgrave Project Editor: Carol Dillingham Editorial Production: Curtis Philips, Technical Reviewer: A lejandro Mesa; Technical Review services provided by Content Master, a member of CM Group, : Roger LeBlanc Proofreader: Andrea Fox Indexer: William P.

3 Meyers Cover: Twist Creative Seattle and Joel PanchotTo Lilach, for giving meaning to everything that I do. ItzIk Contents at a glanceForeword xvIntroduction xviiCHAPTER 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 date and time 419 CHAPTER 8 T-SQL for BI practitioners 473 CHAPTER 9 Programmable objects 525 CHAPTER 10 In-Memory OLTP 671 CHAPTER 11 Graphs and recursive queries 707 Index 803

4 VContentsForeword ..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.

5 38 Conclusion ..39 Chapter 2 Query tuning 41 Internals ..41 Pages and extents ..42 Table organization ..43 Tools to measure query performance ..53 What 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 learning resources for you. To participate in a brief online survey, please visit: ContentsAccess 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.

6 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.

7 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 Contents viiChapter 3 Multi-table queries 187 Subqueries.

8 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.

9 249 The UNION ALL and UNION operators ..250 The INTERSECT operator ..253 The EXCEPT operator ..255 Conclusion ..257viii ContentsChapter 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.

10 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 Contents ixOptimization of OFFSET-FETCH.