Transcription
Foreword by César Galindo-Legaria, PhDManager, Query Optimization Team, Microsoft SQL ServerInside MicrosoftSQL Server 2008: T-SQL QueryingItzik Ben-GanLubor Kollar, Dejan Sarka, Steve KassKalen Delaney–Series Editor
PUBLISHED BYMicrosoft PressA Division of Microsoft CorporationOne Microsoft WayRedmond, Washington 98052-6399Copyright 2009 by Itzik Ben-GanAll rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any meanswithout the written permission of the publisher.Library of Congress Control Number: 2009920791Printed and bound in the United States of America.1 2 3 4 5 6 7 8 9 QWT 4 3 2 1 0 9Distributed in Canada by H.B. Fenn and Company Ltd.A CIP catalogue record for this book is available from the British Library.Microsoft Press books are available through booksellers and distributors worldwide. For further infor mation aboutinternational editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly atfax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to mspinput@microsoft.com.Microsoft, Microsoft Press, Excel, MS, MSDN, PivotTable, SQL Server, Visual Basic, Visual C#, Visual Studio and Windowsare either registered trademarks or trademarks of the Microsoft group of companies. Other product and company namesmentioned herein may be the trademarks of their respective owners.The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and eventsdepicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address,logo, person, place, or event is intended or should be inferred.This book expresses the author’s views and opinions. The information contained in this book is provided without anyexpress, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors willbe held liable for any damages caused or alleged to be caused either directly or indirectly by this book.Acquisitions Editor: Ken JonesDevelopmental Editor: Sally StickneyProject Editor: Denise BankaitisEditorial Production: S4Carlisle Publishing ServicesTechnical Reviewers: Steve Kass and Umachandar Jayachandran; Technical Review services provided by ContentMaster, a member of CM Group, Ltd.Cover: Tom Draper DesignBody Part No. X15-45856
To my parents, Mila & Gabi—Itzik Ben-Gan
Table of ContentsForeword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiiiAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvIntroduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix1Logical Query Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1Logical Query Processing Phases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2Logical Query Processing Phases in Brief . . . . . . . . . . . . . . . . . . . . . . . . . . . 3Sample Query Based on Customers/Orders Scenario . . . . . . . . . . . . . . . . . . . . . . 5Logical Query Processing Phase Details. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Step 1: The FROM Phase. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Step 2: The WHERE Phase. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11Step 3: The GROUP BY Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Step 4: The HAVING Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13Step 5: The SELECT Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Step 6: The Presentation ORDER BY Phase . . . . . . . . . . . . . . . . . . . . . . . . . 16Further Aspects of Logical Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Table Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20OVER Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332Set Theory and Predicate Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . 35An Example of English-to-Mathematics Translation . . . . . . . . . . . . . . . . . . . . . . 35Well-Definedness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Equality, Identity, and Sameness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Mathematical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Functions, Parameters, and Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Instructions and Algorithms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43What 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:www.microsoft.com/learning/booksurvey/v
viTable of ContentsSet Theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Notation for Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Well-Definedness of Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Domains of Discourse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Faithfulness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Russell’s Paradox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52Ordered Pairs, Tuples, and Cartesian Products. . . . . . . . . . . . . . . . . . . . . . 53The Empty Set(s). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54The Characteristic Function of a Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61Set Partitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63Generalizations of Set Theory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Predicate Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65Logic-Like Features of Programming Languages . . . . . . . . . . . . . . . . . . . . 65Propositions and Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66The Law of Excluded Middle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68And, Or, and Not . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68Logical Equivalence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Logical Implication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Quantification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Alternatives and Generalizations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73Relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75The Reflexive, Symmetric, and Transitive Properties . . . . . . . . . . . . . . . . . 75A Practical Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813The Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Introduction to the Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Relations, Tuples and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84The Relational Model: A Quick Summary . . . . . . . . . . . . . . . . . . . . . . . . . . 89Relational Algebra and Relational Calculus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Basic Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90Relational Algebra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91Relational Calculus. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102T-SQL Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104Declarative Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105Other Means of Enforcing Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Table of ContentsNormalization and Other Design Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Normal Forms Dealing with Functional Dependencies. . . . . . . . . . . . . . 112Higher Normal Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119Denormalization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122Generalization and Specialization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1264Query Tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Sample Data for This Chapter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Tuning Methodology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131Analyze Waits at the Instance Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134Correlate Waits with Queues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143Determine Course of Action . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145Drill Down to the Database/File Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145Drill Down to the Process Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148Tune Indexes and Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169Tools for Query Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Cached Query Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Clearing the Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Dynamic Management Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172STATISTICS IO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Measuring the Run Time of Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173Analyzing Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Traces/Profiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186Database Engine Tuning Advisor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Data Collection and Management Data Warehouse. . . . . . . . . . . . . . . . 187Using SMO to Clone Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Index Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Table and Index Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188Index Access Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197Analysis of Indexing Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244Fragmentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258Preparing Sample Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259Data Preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259TABLESAMPLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265An Examination of Set-Based vs. Iterative/ProceduralApproaches and a Tuning Exercise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276vii
viiiTable of Contents5Algorithms and Complexity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277Do You Have a Quarter? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278How Algorithms Scale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279An Example of Quadratic Scaling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280An Algorithm with Linear Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280Exponential and Superexponential Complexity . . . . . . . . . . . . . . . . . . . . 281Sublinear Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282Constant Complexity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Technical Definitions of Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283Comparing Complexities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285Classic Algorithms and Algorithmic Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . 286Algorithms for Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287String Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289A Practical Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290Identifying Trends in Measurement Data . . . . . . . . . . . . . . . . . . . . . . . . . 291The Algorithmic Complexity of LISLP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291Solving the Longest Increasing Subsequence LengthProblem in T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2956Subqueries, Table Expressions, and Ranking Functions . . . . . . 297Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298Self-Contained Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298Correlated Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302Misbehaving Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314Uncommon Predicates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Analytical Ranking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330Row Number. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332Rank and Dense Rank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352Tile Number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354Auxiliary Table of Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359Missing and Existing Ranges (Also Known as Gaps and Islands) . . . . . . . . . . . 363Missing Ranges (Gaps). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366Existing Ranges (Islands) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Table of Contents7Joins and Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389Old Style vs. New Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389Fundamental Join Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390Further Examples of Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402Sliding Total of Previous Year. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417Join Algorithms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421Separating Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436EXCEPT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437INTERSECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439Precedence of Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440Using INTO with Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441Circumventing Unsupported Logical Phases. . . . . . . . . . . . . . . . . . . . . . . 441Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4438Aggregating and Pivoting Data . . . . . . . . . . . . . . . . . . . . . . . . . . 445OVER Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445Tiebreakers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448Running Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451Cumulative Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453Sliding Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457Year-to-Date (YTD) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459Pivoting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460Pivoting Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460Relational Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465Aggregating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466Unpivoting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470Custom Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473Custom Aggregations Using Pivoting . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474User Defined Aggregates (UDA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476Specialized Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487Histograms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499Grouping Factor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503Grouping Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506Sample Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507The GROUPING SETS Subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508ix
xTable of ContentsThe CUBE Subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511The ROLLUP Subclause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512Grouping Sets Algebra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514The GROUPING ID Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518Materialize Grouping Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5259TOP and APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527SELECT TOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527TOP and Determinism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529TOP and Input Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530TOP and Modifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531TOP on Steroids . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535Solutions to Common Problems Using TOP and APPLY . . . . . . . . . . . . . . . . . . 537TOP n for Each Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537Matching Current and Previous Occurrences . . . . . . . . . . . . . . . . . . . . . . 543Paging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547Random Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552Median. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554Logical Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55910 Data Modification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561Inserting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561Enhanced VALUES Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561SELECT INTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563BULK Rowset Provider. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565Minimally Logged Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567INSERT EXEC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590Sequence Mechanisms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595GUIDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601TRUNCATE vs. DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601Removing Rows with Duplicate Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601DELETE Using Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Table of ContentsUpdating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606UPDATE Using Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606Updating Large Value Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610SELECT and UPDATE Statement Assignments . . . . . . . . . . . . . . . . . . . . . . 611Merging Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616MERGE Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617Adding a Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621Multiple WHEN Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623WHEN NOT MATCHED BY SOURCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624MERGE Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626MERGE and Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627OUTPUT Clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628INSERT with OUTPUT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629DELETE with OUTPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630UPDATE with OUTPUT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632MERGE with OUTPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634Composable DML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63811Querying Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639Partitioning in SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639Partitioned Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65712 Graphs, Trees, Hierarchies, and Recursive Queries. . . . . . . . . . . 659Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659Trees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661Employee Organizational Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661Bill of Materials (BOM) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663Road System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 666Iteration/Recursion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670Subordinates. . . . . . . . . . . . . . . . . . . . . . . .
Itzik took a couple of SQL programming problems and diced them up in the most skillful and entertaining way, showing the elegance and effi ciency of set-oriented thinking. The audience loved it—and so did I, except I had a different angle. Having worked on
![SQL Server TSQL [Read-Only]](/img/17/sql-server-tsql.jpg)
