Inside Microsoft SQL Server 2005 : T-SQL Querying

by Sarka, Dejan; Ben-Gan, Itzik

• ISBN13: 9780735623132
• ISBN10: 0735623139
• Edition: 1st
• Format: Paperback
• Copyright: 2006-03-29
• Publisher: Microsoft Press

Summary

Take a detailed look at the internal architecture of T-SQLand unveil the power of set-based queryingwith comprehensive reference and advice from the experts. Database developers and administrators get best practices, sample databases, and code to master the intricacies of the programming languagesolving complex problems with real-world solutions.Discover how to: Understand logical and physical query processing Apply a methodology to optimize query tuning Solve relational division problems Use CTEs and ranking functions to simplify and optimize solutions Aggregate data with various techniques, including tiebreakers, pivoting, histograms, and grouping factors Use the TOP option in a query to modify data Query specialized data structures with recursive logic, materialized path, or nested sets solutions PLUSImprove your logic and get to the heart of querying problems with logic puzzlesGet code and database samples on the Web

Author Biography

Dejan Sarka is a mentor with SolidQ and focuses on development of database and business intelligence applications. He is a frequent speaker at international conferences such as TechEd, SqlDevCon, and PASS. He is the founder of the Slovenian SQL Server and .NET Users Group. As main author or coauthor, Dejan has written nine books about SQL Server. He has also developed three courses for SolidQ: Data Modeling Essentials, Data Quality and Master Data Management, and Data Mining.

Table of Contents

Foreword	p. xiii
Preface	p. xv
Acknowledgments	p. xix
Introduction	p. xxiii
Organization of This Book	p. xxiii
System Requirements	p. xxiii
Installing Sample Databases	p. xxiv
Updates	p. xxiv
Code Samples	p. xxiv
Support for This Book	p. xxiv
Logical Query Processing	p. 1
Logical Query Processing Phases	p. 3
Brief Description of Logical Query Processing Phases	p. 4
Sample Query Based on Customers/Orders Scenario	p. 4
Logical Query Processing Phase Details	p. 6
Performing a Cartesian Product (Cross Join)	p. 6
Applying the ON Filter (Join Condition)	p. 8
Adding Outer Rows	p. 10
Applying the WHERE Filter	p. 11
Grouping	p. 12
Applying the CUBE or ROLLUP Option	p. 13
Applying the HAVING Filter	p. 13
Processing the SELECT List	p. 14
Applying the DISTINCT Clause	p. 15
Applying the ORDER BY Clause	p. 15
Applying the TOP Option	p. 18
New Logical Processing Phases in SQL Server 2005	p. 19
Table Operators	p. 19
OVER Clause	p. 27
Set Operations	p. 29
Conclusion	p. 30
Physical Query Processing	p. 31
Flow of Data During Query Processing	p. 32
Compilation	p. 35
Algebrizer	p. 37
Optimization	p. 40
Working with the Query Plan	p. 47
Update Plans	p. 59
Conclusion	p. 63
Acknowledgment	p. 63
Query Tuning	p. 65
Sample Data for This Chapter	p. 66
Tuning Methodology	p. 69
Analyze Waits at the Instance Level	p. 71
Correlate Waits with Queues	p. 80
Determine Course of Action	p. 81
Drill Down to the Database/File Level	p. 82
Drill Down to the Process Level	p. 84
Tune Indexes/Queries	p. 103
Tools for Query Tuning	p. 105
syscacheobjects	p. 105
Clearing the Cache	p. 105
Dynamic Management Objects	p. 106
STATISTICS IO	p. 106
Measuring the Run Time of Queries	p. 106
Analyzing Execution Plans	p. 107
Hints	p. 119
Traces/Profiler	p. 121
Database Engine Tuning Advisor	p. 121
Index Tuning	p. 122
Table and Index Structures	p. 122
Index Access Methods	p. 132
Index Optimization Scale	p. 155
Fragmentation	p. 168
Partitioning	p. 170
Preparing Sample Data	p. 170
Data Preparation	p. 170
TABLESAMPLE	p. 177
An Examination of Set-Based vs. Iterative/Procedural Approaches, and a Tuning Exercise	p. 180
Additional Resources	p. 187
Conclusion	p. 189
Subqueries, Table Expressions, and Ranking Functions	p. 191
Subqueries	p. 191
Self-Contained Subqueries	p. 192
Correlated Subqueries	p. 195
Misbehaving Subqueries	p. 208
Uncommon Predicates	p. 209
Table Expressions	p. 211
Derived Tables	p. 211
Common Table Expressions (CTE)	p. 214
Analytical Ranking Functions	p. 222
Row Number	p. 224
Rank and Dense Rank	p. 246
NTILE	p. 247
Auxiliary Table of Numbers	p. 252
Existing and Missing Ranges (Also Known as Islands and Gaps)	p. 256
Missing Ranges (Also Known as Gaps)	p. 257
Existing Ranges (Also Known as Islands)	p. 260
Conclusion	p. 262
Joins and Set Operations	p. 263
Joins	p. 263
Old Style vs. New Style	p. 263
Fundamental Join Types	p. 264
Further Examples of Joins	p. 276
Sliding Total of Previous Year	p. 287
Join Algorithms	p. 291
Separating Elements	p. 296
Set Operations	p. 303
UNION	p. 304
EXCEPT	p. 305
INTERSECT	p. 307
Precedence of Set Operations	p. 309
Using INTO with Set Operations	p. 310
Circumventing Unsupported Logical Phases	p. 310
Conclusion	p. 313
Aggregating and Pivoting Data	p. 315
OVER Clause	p. 315
Tiebreakers	p. 319
Running Aggregations	p. 321
Cumulative Aggregations	p. 323
Sliding Aggregations	p. 328
Year-To-Date (YTD)	p. 330
Pivoting	p. 331
Pivoting Attributes	p. 331
Relational Division	p. 335
Aggregating Data	p. 337
Unpivoting	p. 341
Custom Aggregations	p. 344
Custom Aggregations Using Pivoting	p. 345
User Defined Aggregates (UDA)	p. 347
Specialized Solutions	p. 358
Histograms	p. 367
Grouping Factor	p. 371
CUBE and ROLLUP	p. 374
CUBE	p. 374
Rollup	p. 379
Conclusion	p. 380
TOP and APPLY	p. 381
SELECT TOP	p. 381
TOP and Determinism	p. 383
TOP and Input Expressions	p. 385
TOP and Modifications	p. 385
APPLY	p. 388
Solutions to Common Problems Using TOP and APPLY	p. 391
TOP n for Each Group	p. 391
Matching Current and Previous Occurrences	p. 397
Paging	p. 402
Random Rows	p. 411
Median	p. 413
Conclusion	p. 415
Data Modification	p. 417
Inserting Data	p. 417
SELECT INTO	p. 417
INSERT EXEC	p. 419
Inserting New Rows	p. 423
INSERT with OUTPUT	p. 426
Sequence Mechanisms	p. 428
Deleting Data	p. 435
TRUNCATE vs. DELETE	p. 435
Removing Rows with Duplicate Data	p. 435
DELETE Using Joins	p. 438
DELETE with OUTPUT	p. 441
Updating Data	p. 443
UPDATE Using Joins	p. 443
UPDATE with OUTPUT	p. 447
SELECT and UPDATE Statement Assignments	p. 450
Other Performance Considerations	p. 454
Conclusion	p. 457
Graphs, Trees, Hierarchies, and Recursive Queries	p. 459
Terminology	p. 460
Graphs	p. 460
Trees	p. 461
Hierarchies	p. 461
Scenarios	p. 462
Employee Organizational Chart	p. 462
Bill of Materials (BOM)	p. 464
Road System	p. 468
Iteration/Recursion	p. 471
Subordinates	p. 472
Ancestors	p. 484
Subgraph/Subtree with Path Enumeration	p. 487
Sorting	p. 491
Cycles	p. 502
Materialized Path	p. 505
Maintaining Data	p. 506
Querying	p. 512
Nested Sets	p. 517
Assigning Left and Right Values	p. 518
Querying	p. 527
Transitive Closure	p. 530
Directed Acyclic Graph	p. 531
Conclusion	p. 548
Logic Puzzles	p. 551
Puzzles	p. 551
Medication Tablets	p. 551
Chocolate Bar	p. 552
To a T	p. 552
On the Dot	p. 553
Rectangles in a Square	p. 553
Measuring Time by Burning Ropes	p. 553
Arithmetic Maximum Calculation	p. 554
Covering a Chessboard with Domino Tiles	p. 554
The Missing Buck	p. 555
Flipping Lamp Switches	p. 555
Cutting a Stick to Make a Triangle	p. 555
Rectangle Within a Circle	p. 555
Monty Hall Problem	p. 556
Piece of Cake	p. 556
Cards Facing Up	p. 556
Basic Arithmetic	p. 557
Self-Replicating Code (Quine)	p. 557
Hiking a Mountain	p. 557
Find the Pattern in the Sequence	p. 558
Puzzle Solutions	p. 558
Medication Tablets	p. 558
Chocolate Bar	p. 558
To a T	p. 558
On the Dot	p. 559
Rectangles in a Square	p. 559
Measuring Time by Burning Ropes	p. 561
Arithmetic Maximum Calculation	p. 561
Covering a Chessboard with Domino Tiles	p. 561
The Missing Buck	p. 562
Alternating Lamp States	p. 562
Cutting a Stick to Make a Triangle	p. 562
Rectangle Within a Circle	p. 563
Monty Hall Problem	p. 563
Piece of Cake	p. 565
Cards Facing Up	p. 565
Basic Arithmetic	p. 565
Self-Replicating Code (Quine)	p. 566
Hiking a Mountain	p. 566
Find the Pattern in the Sequence	p. 567
Conclusion	p. 567
Table of Contents provided by Ingram. All Rights Reserved.