Inside Microsoft SQL server 2005 : T-SQL querying

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.

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
1 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
Step 1 Performing a Cartesian Product (Cross Join)	p. 6
Step 2 Applying the ON Filter (Join Condition)	p. 8
Step 3 Adding Outer Rows	p. 10
Step 4 Applying the WHERE Filter	p. 11
Step 5 Grouping	p. 12
Step 6 Applying the CUBE or ROLLUP Option	p. 13
Step 7 Applying the HAVING Filter	p. 13
Step 8 Processing the SELECT List	p. 14
Step 9 Applying the DISTINCT Clause	p. 15
Step 10 Applying the ORDER BY Clause	p. 15
Step 11 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
2 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
3 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
4 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
4 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
6 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
7 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
8 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
9 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
A Logic Puzzles	p. 551
Puzzles	p. 551
Puzzle 1 Medication Tablets	p. 551
Puzzle 2 Chocolate Bar	p. 552
Puzzle 3 To a T	p. 552
Puzzle 4 On the Dot	p. 553
Puzzle 5 Rectangles in a Square	p. 553
Puzzle 6 Measuring Time by Burning Ropes	p. 553
Puzzle 7 Arithmetic Maximum Calculation	p. 554
Puzzle 8 Covering a Chessboard with Domino Tiles	p. 554
Puzzle 9 The Missing Buck	p. 555
Puzzle 10 Flipping Lamp Switches	p. 555
Puzzle 11 Cutting a Stick to Make a Triangle	p. 555
Puzzle 12 Rectangle Within a Circle	p. 555
Puzzle 13 Monty Hall Problem	p. 556
Puzzle 14 Piece of Cake	p. 556
Puzzle 15 Cards Facing Up	p. 556
Puzzle 16 Basic Arithmetic	p. 557
Puzzle 17 Self-Replicating Code (Quine)	p. 557
Puzzle 18 Hiking a Mountain	p. 557
Puzzle 19 Find the Pattern in the Sequence	p. 558
Puzzle Solutions	p. 558
Puzzle 1 Medication Tablets	p. 558
Puzzle 2 Chocolate Bar	p. 558
Puzzle 3 To a T	p. 558
Puzzle 4 On the Dot	p. 559
Puzzle 5 Rectangles in a Square	p. 559
Puzzle 6 Measuring Time by Burning Ropes	p. 561
Puzzle 7 Arithmetic Maximum Calculation	p. 561
Puzzle 8 Covering a Chessboard with Domino Tiles	p. 561
Puzzle 9 The Missing Buck	p. 562
Puzzle 10 Alternating Lamp States	p. 562
Puzzle 11 Cutting a Stick to Make a Triangle	p. 562
Puzzle 12 Rectangle Within a Circle	p. 563
Puzzle 13 Monty Hall Problem	p. 563
Puzzle 14 Piece of Cake	p. 565
Puzzle 15 Cards Facing Up	p. 565
Puzzle 16 Basic Arithmetic	p. 565
Puzzle 17 Self-Replicating Code (Quine)	p. 566
Puzzle 18 Hiking a Mountain	p. 566
Puzzle 19 Find the Pattern in the Sequence	p. 567
Conclusion	p. 567

Available:*

On Order

Summary

Summary

Author Notes

Table of Contents