T-SQL Querying Itzik Ben-Gan Dejan Sarka Adam Machanic Kevin Farlee PUBLISHED BY Microsoft Press A Division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 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 without the written permission of the publisher. Library of Congress Control Number: 2014951866 ISBN: 978-0-7356-8504-8 Printed and bound in the United States of America. First Printing Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at [email protected]. Please tell us what you think of this book at http://aka.ms/tellpress. This book is provided “as-is” and expresses the authors’ views and opinions. The views, opinions, and information expressed 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 or connection is intended or should be inferred. Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” webpage are trademarks of the Microsoft group of companies. All other marks are the property of their respective owners. Acquisitions Editor: Devon Musgrave Developmental Editor: Devon Musgrave Project Editor: Carol Dillingham Editorial Production: Curtis Philips, Publishing.com Technical Reviewer: Alejandro Mesa; Technical Review services provided by Content Master, a member of CM Group, Ltd. Copyeditor: Roger LeBlanc Proofreader: Andrea Fox Indexer: William P. Meyers Cover: Twist Creative • Seattle and Joel Panchot To Lilach, for giving meaning to everything that I do. —ITZIK Contents at a glance Foreword Introduction CHAPTER 1 Logical query processing CHAPTER 2 Query tuning CHAPTER 3 Multi-table queries CHAPTER 4 Grouping, pivoting, and windowing CHAPTER 5 TOP and OFFSET-FETCH CHAPTER 6 Data modification CHAPTER 7 Working with date and time CHAPTER 8 T-SQL for BI practitioners CHAPTER 9 Programmable objects CHAPTER 10 In-Memory OLTP CHAPTER 11 Graphs and recursive queries Index Contents Foreword Introduction Chapter 1 Logical query processing Logical query-processing phases Logical query-processing phases in brief Sample query based on customers/orders scenario Logical query-processing phase details Step 1: The FROM phase Step 2: The WHERE phase Step 3: The GROUP BY phase Step 4: The HAVING phase Step 5: The SELECT phase Step 6: The ORDER BY phase Step 7: Apply the TOP or OFFSET-FETCH filter Further aspects of logical query processing Table operators Window functions The UNION, EXCEPT, and INTERSECT operators Conclusion Chapter 2 Query tuning Internals Pages and extents Table organization Tools to measure query performance Access methods Table scan/unordered clustered index scan Unordered covering nonclustered index scan Ordered clustered index scan Ordered covering nonclustered index scan The storage engine’s treatment of scans Nonclustered index seek + range scan + lookups Unordered nonclustered index scan + lookups Clustered index seek + range scan Covering nonclustered index seek + range scan Cardinality estimates Legacy estimator vs. 2014 cardinality estimator Implications of underestimations and overestimations Statistics Estimates for multiple predicates Ascending key problem Unknowns Indexing features Descending indexes Included non-key columns Filtered indexes and statistics Columnstore indexes Inline index definition Prioritizing queries for tuning with extended events Index and query information and statistics Temporary objects Set-based vs. iterative solutions Query tuning with query revisions Parallel query execution How intraquery parallelism works Parallelism and query optimization The parallel APPLY query pattern Conclusion Chapter 3 Multi-table queries Subqueries Self-contained subqueries Correlated subqueries The EXISTS predicate Misbehaving subqueries Table expressions Derived tables CTEs Views Inline table-valued functions Generating numbers The APPLY operator The CROSS APPLY operator The OUTER APPLY operator Implicit APPLY Reuse of column aliases Joins Cross join Inner join Outer join Self join Equi and nonequi joins Multi-join queries Semi and anti semi joins Join algorithms Separating elements The UNION, EXCEPT, and INTERSECT operators The UNION ALL and UNION operators The INTERSECT operator The EXCEPT operator Conclusion Chapter 4 Grouping, pivoting, and windowing Window functions Aggregate window functions Ranking window functions Offset window functions Statistical window functions Gaps and islands Pivoting One-to-one pivot Many-to-one pivot Unpivoting Unpivoting with CROSS JOIN and VALUES Unpivoting with CROSS APPLY and VALUES Using the UNPIVOT operator Custom aggregations Using a cursor Using pivoting Specialized solutions Grouping sets GROUPING SETS subclause CUBE and ROLLUP clauses Grouping sets algebra Materializing grouping sets Sorting Conclusion Chapter 5 TOP and OFFSET-FETCH The TOP and OFFSET-FETCH filters The TOP filter The OFFSET-FETCH filter Optimization of filters demonstrated through paging Optimization of TOP Optimization of OFFSET-FETCH Optimization of ROW_NUMBER Using the TOP option with modifications TOP with modifications Modifying in chunks Top N per group Solution using ROW_NUMBER Solution using TOP and APPLY Solution using concatenation (a carry-along sort) Median Solution using PERCENTILE_CONT Solution using ROW_NUMBER Solution using OFFSET-FETCH and APPLY Conclusion Chapter 6 Data modification Inserting data SELECT INTO Bulk import Measuring the amount of logging BULK rowset provider Sequences Characteristics and inflexibilities of the identity property The sequence object Performance considerations Summarizing the comparison of identity with sequence Deleting data TRUNCATE TABLE Deleting duplicates Updating data Update using table expressions Update using variables