Advanced Transact−SQL for SQL Server 2000

Table of Contents......Page 2
Advanced Transact-SQL for SQL Server 2000......Page 13
Dedication......Page 15
Foreword......Page 17
The Passing of Knowledge, by Itzik Ben-Gan......Page 19
I Can't Believe I'm Doing This..., by Tom Moreau......Page 22
What You Will See......Page 27
The Latex Manufacturing Company Example......Page 30
Old-Style SQL-89 Two-Way Inner Joins......Page 32
SQL-92 Join Syntax......Page 33
Old-Style SQL-89 Three-Way Inner Joins......Page 34
SQL-92 Three-Way Inner Joins......Page 35
Cross Joins......Page 36
Old-Style Two-Way Left Outer Joins......Page 38
SQL-92 Two-Way Left Outer Joins......Page 39
SQL-92 Two-Way Right Outer Joins......Page 40
SQL-92 Two-Way Full Outer Joins......Page 41
SQL-92 Three-Way Outer Joins......Page 42
The Catch......Page 44
Controlling the Order of Join Processing......Page 46
The Dating Service Scenario......Page 49
The Salary Levels Scenario......Page 53
Using Joins to Delete Data......Page 55
Using Joins to Update Data......Page 56
Completely Qualified Filter Criteria......Page 57
Join Hints......Page 58
SQL Puzzle 1-1: Joins......Page 59
Creating Nested Scalar Subqueries......Page 60
Using the IN Predicate as a Subquery......Page 61
Using Correlated Subqueries......Page 63
Calculating Running Totals......Page 72
Using a SELECT with a GROUP BY as a Derived Table......Page 73
Updating Rows with a GROUP BY Clause......Page 75
Using Unions in Derived Tables......Page 76
Creating Histograms......Page 77
Comparing Performance......Page 78
SQL Puzzle 2-1: Bank Interest......Page 82
SQL Puzzle 2-2: Managing Orders and Their Payments......Page 83
SQL Puzzle 2-3: Finding Unread Messages......Page 85
Using the INSERT Statement......Page 87
Using the INSERT DEFAULT VALUES Statement......Page 88
Using the INSERT VALUES Statement......Page 89
Using the INSERT SELECT Statement......Page 90
Using the INSERT EXEC Statement......Page 91
Using the SELECT INTO Statement......Page 93
Using the BULK INSERT Statement......Page 95
CODEPAGE......Page 96
FIELDTERMINATOR......Page 97
ORDER......Page 98
Loading Data......Page 99
Normalizing Data......Page 100
Generating Test Data......Page 103
SQL Puzzle 3-1: Populating the Customers Table......Page 106
Using the Simple CASE Expression......Page 107
Using the Searched CASE Expression......Page 108
Determining Your Own Sort Order......Page 109
Creating Pivot Tables......Page 110
Using the TOP n Option......Page 112
Using the WITH TIES Option......Page 113
Using the SET ROWCOUNT Option......Page 115
Using Logical Expressions and Bitwise Operations......Page 116
Using Logical Expressions......Page 117
Using Bitwise Operations......Page 123
Operator Precedence......Page 131
Handling Dates......Page 133
Using DATEPART() and YEAR(), MONTH(), and DAY()......Page 134
Using the DATEADD() Function......Page 135
Avoiding WHERE Clause Traps in Date Manipulation......Page 136
Using the CONVERT() Function......Page 137
Finding the Last Day of the Month......Page 138
Finding the Month Name Based on the Month Number......Page 139
SQL Puzzle 4-1: Euro 2000 Them (posted by Colin Rippey)......Page 140
Refining Your GROUP BY Queries......Page 143
CUBE......Page 146
ROLLUP......Page 153
Should I CUBE or ROLLUP?......Page 156
To CUBE or Not to CUBE......Page 158
Using the COMPUTE Option......Page 162
Using the COMPUTE BY Option......Page 164
COMPUTE Considerations......Page 166
SQL Puzzle 5-1: Management Levels......Page 167
Using Unicode Datatypes......Page 168
Using the rowversion Datatype......Page 169
Using Text in Row......Page 171
Using the READTEXT Statement......Page 173
Using the WRITETEXT Statement......Page 174
Using the UPDATETEXT Statement......Page 175
Using the bigint Datatype......Page 177
Using the sql_variant Datatype......Page 178
Using the SQL_VARIANT_PROPERTY() Function......Page 180
Using the table Datatype......Page 182
Creating and Querying Tables with IDENTITY Columns......Page 184
Using the IDENTITY_INSERT Session Option......Page 186
What Did I Just Insert?......Page 187
SQL Puzzle 6-1: Customers with and without Sales......Page 193
Working with Variables......Page 194
Assigning Values with SET and SELECT......Page 195
The IF ELSE Construct......Page 197
The WHILE Construct......Page 199
Managing Error Messages......Page 200
Raising Errors......Page 202
Trapping Errors......Page 206
Coding Styles......Page 207
Tom's Style......Page 208
Itzik's Style......Page 209
Using Transactions......Page 210
Understanding Nested Transactions and @@TRANCOUNT......Page 212
SQL Puzzle 7-1: Eliminating an Explicit Transaction......Page 213
Each Column Must Have a Name and the Name Must Be Unique......Page 214
Cannot Use the ORDER BY Clause in a View......Page 216
Hiding the Complexity of the Underlying Query......Page 219
Using Views as a Security Mechanism......Page 220
Altering a View......Page 222
Encrypting the View Definition......Page 223
Using the SCHEMABINDING Option......Page 224
Using Views to Modify Data......Page 225
Using the CHECK Option......Page 226
Limitations to Modifying Data through Views......Page 227
Updating Views through SQL Server Enterprise Manager......Page 232
Tuning without Indexed Views......Page 233
Tuning with Indexed Views......Page 236
Using the Index Tuning Wizard to Recommend Indexed Views......Page 239
SQL Puzzle 8-1: Updateable Ordered View (by Zoltan Kovacs)......Page 243
The CREATE PROCEDURE Statement......Page 244
The DROP PROCEDURE Statement......Page 248
Calling a Stored Procedure......Page 249
Using WITH RECOMPILE......Page 251
Using Return Codes and the RETURN Statement......Page 255
Leveraging Dynamic Execution with the EXEC () Statement......Page 256
Leveraging Dynamic Execution with sp_executesql......Page 257
ANSI_DEFAULTS......Page 260
TRANSACTION ISOLATION LEVEL......Page 261
LOCK_TIMEOUT......Page 262
Useful System Stored Procedures......Page 263
Creating System Stored Procedures......Page 265
Nesting Stored Procedures......Page 266
Recursion......Page 267
Security and Stored Procedures......Page 268
Statements Not Permitted Inside a Stored Procedure......Page 269
Transactions and Stored Procedures......Page 270
SQL Puzzle 9-2: Automatically Creating Stored Procedures......Page 271
Using the CREATE TRIGGER Statement......Page 272
Using the DROP TRIGGER Statement......Page 274
Firing Triggers......Page 275
First and Last Triggers......Page 276
Using the IF UPDATE() and IF COLUMNS_UPDATED() Functions......Page 277
Other Locking Issues......Page 279
Disabling Triggers and Constraints......Page 280
Using @@IDENTITY and SCOPE_IDENTITY() with Triggers......Page 281
Understanding AFTER Triggers......Page 283
Understanding INSTEAD OF Triggers......Page 286
Keeping the Previous Image......Page 291
Keeping All Images......Page 292
Auditing Last UpdatedWhen and By Whom......Page 293
Statements Not Permitted Inside a Trigger......Page 294
SQL Puzzle 10-1: Pending Deletes......Page 295
Scalar Functions......Page 296
Inline Table-Valued Functions......Page 301
Multistatement Table-Valued Functions......Page 304
Supplied User-Defined System Functions......Page 306
Creating Your Own User-Defined System Functions......Page 309
Using Default Values......Page 310
Determinism, Schemabinding, and Participating in Constraints and Indices......Page 311
Understanding Deferred Name Resolution and Recursion......Page 316
Using User-Defined Functions to Manipulate Complex Numbers......Page 318
Why Use Complex Numbers in T-SQL?......Page 319
Validating, Representing, and Extracting the Parts of Complex Numbers......Page 320
Complex Algebra, Geometry, and Arithmetic Operations......Page 325
Polar Forms of Complex Numbers and Calculating Vector Sizes......Page 330
Implementations of the Complex Functions......Page 332
SQL Puzzle 11-1: Formatting Dates......Page 336
Creating Temporary Tables......Page 338
Creating Global Temporary Tables......Page 339
Using Temporary Tables......Page 340
Avoiding Repetitive Work by Using a Temporary Table......Page 341
Using a Temporary Table to Handle Weird Business Rules......Page 349
Using Temporary Tables to Communicate with the EXEC() Statement......Page 351
Removing Duplicates with and without Temporary Tables......Page 352
SQL Puzzle 12-1: Creating a Temporary Table and Cursor-Free Solution......Page 354
Setting Up Local Horizontally Partitioned Views......Page 356
Modifying Local Horizontally Partitioned Views......Page 358
Querying Local Horizontally Partitioned Views......Page 360
Distributed (Federated) Horizontally Partitioned Views......Page 362
The Orders and OrderDetails Tables......Page 385
The Employees Table......Page 386
NO ACTION (restrict)......Page 388
CASCADE......Page 390
Deleting a Row in the Primary Table......Page 394
Updating a Row in the Primary Table......Page 395
Inserting a Row into the Primary Table......Page 396
Inserting, Updating, and Deleting a Row in the Secondary Table......Page 397
Using Triggers Prior to SQL Server 2000......Page 398
Using Triggers in SQL Server 2000......Page 413
SQL Puzzle 14-1: Implementing Cascading Operations......Page 417
The Top 5 Reasons People Use Cursors......Page 419
Implementing Cursors......Page 420
Using the DECLARE Statement......Page 421
Using the FETCH Statement......Page 423
Using the CLOSE Statement......Page 425
Using Cursor Variables with Stored Procedures......Page 426
Cursor Close on Commit......Page 427
Using Cursors on Stored Procedure Calls......Page 428
Putting Cursors to Worka Practical Example......Page 429
SQL Alternatives to Cursors......Page 430
WHILE Loops......Page 432
Useful Stored Procedures......Page 435
Best Practices......Page 436
SQL Puzzle 15-1: Discounting Scheme......Page 437
Creating the Employees Table......Page 438
Querying the Employees Table......Page 440
Problematic Questions......Page 442
Providing a Solution by Adding Information to the Table......Page 443
A Self-Maintained Solution......Page 444
Answering the Problematic Questions......Page 450
Answering Questions with User-Defined Functions......Page 456
Maintaining Data Modification......Page 457
Removing Employees......Page 460
Repopulating the Employees Table......Page 463
Using Indexes and Running Performance Tests......Page 465
SQL Puzzle 16-1: Hierarchies and User-Defined Functions......Page 471
Unlearning an Old Trick......Page 472
Getting NULLs to Sort Last Instead of First......Page 473
Using a Parameter for the Column in the ORDER BY Clause (by Bruce P. Margolin)......Page 474
Using the Column Number as Parameter and CASE to Determine the Column......Page 475
Using Dynamic Execution......Page 476
Formatting Output that May Be Null (by Robert Skoglund)......Page 477
Using the INSERT EXEC Statement......Page 478
Using the OPENROWSET() Function......Page 479
Using OPENROWSET() in a View......Page 480
Choosing between SQL and OPENQUERY()......Page 481
Using CASE in a JOIN (by Robert Vieira)......Page 482
Using COALESCE() with a LEFT JOIN......Page 483
Case-Sensitive Searches (by Umachandar Jayachandran)......Page 485
Getting Correct Values from @@ Functions......Page 486
Using PWDCOMPARE() and PWDENCRYPT() in SQL Server 6.5 and 7.0 (by Brian Moran)......Page 488
Creating Sorted Views......Page 490
Getting Rows m to n......Page 491
Getting the First n Rows for Each Occurrence of......Page 493
Are You Being Served?......Page 496
SQL Puzzle 17-1: Top Gun: The Best of the Best......Page 498
What Is a Magic Square?......Page 499
SQL Puzzle 17-2-1: Filling a Table with a Magic Square's Data in T-SQL......Page 500
SQL Puzzle 17-2-3: Checking whether the Table Represents a Magic Square Correctly......Page 501
SQL Puzzle 1: Joins......Page 502
SQL Puzzle 2-1: Bank Interest......Page 503
SQL Puzzle 2-2: Managing Orders and Their Payments......Page 504
SQL Puzzle 2-3: Finding Unread Messages......Page 507
SQL Puzzle 3-1: Populating the Customers Table......Page 508
SQL Puzzle 4-1: Euro 2000 Theme......Page 510
SQL Puzzle 5-1: Management Levels......Page 512
SQL Puzzle 6-1: Customers with and without Sales......Page 514
SQL Puzzle 8-1: Updateable Ordered View......Page 515
SQL Puzzle 9-2: Automatically Creating Stored Procedures......Page 516
SQL Puzzle 10-1: Pending Deletes......Page 518
SQL Puzzle 11-1: Formatting Dates......Page 519
SQL Puzzle 12-1: Creating a Temporary Table and Cursor-Free Solution......Page 521
SQL Puzzle 14-1: Implementing Cascading Operations......Page 524
SQL Puzzle 15-1: Discounting Scheme......Page 531
SQL Puzzle 16-1: Hierarchies and User-Defined Functions......Page 532
SQL Puzzle 17-1: Top Gun: The Best of the Best......Page 534
SQL Puzzle 17-2-1: Filling a Table with a Magic Square's Data in T-SQL......Page 535
SQL Puzzle 17-2-2: Displaying the Magic Square as a Cross-Tab Table......Page 538
SQL Puzzle 17-2-3: Checking whether the Table Represents a Magic Square Correctly......Page 539
The SELECT Clause......Page 542
The WHERE Clause......Page 543
The GROUP BY Clause......Page 544
The INSERT Clause......Page 545
The Derived Table Clause......Page 546
The DEFAULT VALUES Clause......Page 547
The SET Clause......Page 548
The DELETE Clause......Page 549
The WHERE Clause......Page 550
Appendix B: Checking for ANSI Compatibility with SET FIPS_FLAGGER......Page 551
System Monitor (Performance Monitor)......Page 553
Profiler......Page 555
SQL Enterprise Manager......Page 557
Query Analyzer......Page 558
System Stored Procedures......Page 562
SET Options......Page 563
Is It a SQL Server Problem?......Page 564
Which Query Is Slow?......Page 565
Are All Objects Actually Being Used?......Page 566
Will an Index Help or Hinder?......Page 567
Are Loops and Cursors Necessary?......Page 568
Volume and Load Testing......Page 569
Guidelines for Granting Permissions......Page 570
Dynamic Execution......Page 571
Object References......Page 572
Appendix E: pubs and Northwind Database Schemas......Page 573
Creating a Delete Cascade Trigger on the Primary (Referenced) Table......Page 575
Creating a Prevent Delete Trigger on the Primary (Referenced) Table......Page 576
Creating an Update Cascade Trigger on the Primary (Referenced) Table......Page 577
Creating a Prevent Update Trigger on the Primary (Referenced) Table......Page 578
Creating a Prevent Insert or Update Trigger on the Secondary (Referencing) Table......Page 579
Encapsulating the Logic......Page 580
Testing the sp_CreateRelationship Stored Procedure......Page 581
SQL Server Professional (http://www.pinnaclepublishing.com/SQ)......Page 584
Books......Page 585