SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach (Books for Professionals by Professionals)

SQL Server 2008 Transact-SQL Recipes......Page 1 Contents at a Glance......Page 6 Contents......Page 9 About the Author......Page 27 About the Technical Reviewer......Page 29 Acknowledgments......Page 31 Introduction......Page 33 The Basic SELECT Statement......Page 35 How It Works......Page 36 Selective Querying Using a Basic WHERE Clause......Page 37 Combining Search Conditions......Page 38 How It Works......Page 39 Keeping Your WHERE Clause Unambiguous......Page 40 Using Operators and Expressions......Page 41 Using Comparisons......Page 43 How It Works......Page 44 Using Wildcards with LIKE......Page 45 Declaring and Assigning Values to Variables......Page 46 How It Works......Page 47 How It Works......Page 48 Using GROUP BY ALL......Page 49 Selectively Querying Grouped Data Using HAVING......Page 50 Using the ORDER BY Clause......Page 51 How It Works......Page 52 Using the TOP Keyword with Ordered Results......Page 53 How It Works......Page 54 Using DISTINCT to Remove Duplicate Values......Page 55 Using Column Aliases......Page 56 Using SELECT to Create a Script......Page 57 Performing String Concatenation......Page 58 How It Works......Page 59 Using the INTO Clause......Page 60 Using Subqueries to Check for Matches......Page 61 Querying from More Than One Data Source......Page 62 How It Works......Page 63 Using OUTER Joins......Page 64 Using CROSS Joins......Page 65 How It Works......Page 66 Combining Result Sets with UNION......Page 67 How It Works......Page 68 Using CROSS APPLY......Page 69 How It Works......Page 70 Using OUTER APPLY......Page 71 Using the TABLESAMPLE to Return Random Rows......Page 72 Using PIVOT to Convert Single Column Values into Multiple Columns and Aggregate Data......Page 73 How It Works......Page 75 Normalizing Data with UNPIVOT......Page 76 How It Works......Page 77 Returning Distinct or Matching Rows Using EXCEPT and INTERSECT......Page 78 How It Works......Page 79 Summarizing Data Using CUBE......Page 80 Summarizing Data Using ROLLUP......Page 82 Creating Custom Summaries Using Grouping Sets......Page 83 Revealing Rows Generated by GROUPING......Page 85 How It Works......Page 86 Advanced Group-Level Identification with GROUPING_ID......Page 87 How It Works......Page 89 Using a Non-Recursive Common Table Expression......Page 90 How It Works......Page 92 Using a Recursive Common Table Expression......Page 93 How It Works......Page 94 INSERT......Page 97 How It Works......Page 98 Inserting a Row Using Default Values......Page 99 Explicitly Inserting a Value into an IDENTITY Column......Page 100 Inserting a Row into a Table with a uniqueidentifier Column......Page 101 Inserting Rows Using an INSERT...SELECT Statement......Page 102 How It Works......Page 103 Inserting Data from a Stored Procedure Call......Page 104 Inserting Multiple Rows with VALUES......Page 105 Using VALUES As a Table Source......Page 106 UPDATE......Page 107 How It Works......Page 108 How It Works......Page 109 Updating Large Value Data Type Columns......Page 110 How It Works......Page 111 Inserting or Updating an Image File Using OPENROWSET and BULK......Page 112 How It Works......Page 113 Storing Unstructured Data on the File System While Maintaining SQL Server Transactional Control......Page 114 How It Works......Page 117 Assigning and Modifying Database Values “in Place”......Page 118 Deleting Rows......Page 120 How It Works......Page 121 How It Works......Page 122 Chunking Data Modifications with TOP......Page 123 Executing INSERTs, UPDATEs, and DELETEs in a Single Statement......Page 124 How It Works......Page 126 Returning Rows Affected by a Data Modification Statement......Page 127 How It Works......Page 129 Asynchronously Capturing Table Data Modifications......Page 130 Querying All Changes from CDC Tables......Page 133 How It Works......Page 136 Querying Net Changes from CDC Tables......Page 137 Translating the CDC Update Mask......Page 138 Working with LSN Boundaries......Page 139 How It Works......Page 140 Tracking Net Data Changes with Minimal Disk Overhead......Page 141 How It Works......Page 147 Transaction Control......Page 149 Using Explicit Transactions......Page 151 How It Works......Page 152 Displaying the Oldest Active Transaction with DBCC OPENTRAN......Page 153 Querying Transaction Information by Session......Page 154 Locking......Page 156 Viewing Lock Activity......Page 158 How It Works......Page 159 Controlling a Table’s Lock Escalation Behavior......Page 160 How It Works......Page 161 Transaction, Locking, and Concurrency......Page 162 Configuring a Session’s Transaction Locking Behavior......Page 163 How It Works......Page 166 Identifying and Resolving Blocking Issues......Page 168 Configuring How Long a Statement Will Wait for a Lock to Be Released......Page 170 Deadlocking......Page 171 Identifying Deadlocks with a Trace Flag......Page 172 Setting Deadlock Priority......Page 175 How It Works......Page 176 Table Basics......Page 177 Adding a Column to an Existing Table......Page 181 Changing an Existing Column Definition......Page 182 Creating a Computed Column......Page 183 Reducing Storage for Null Columns......Page 184 Dropping a Table Column......Page 187 Dropping a Table......Page 188 Viewing Collation Metadata......Page 189 Designating a Column’s Collation......Page 190 Keys......Page 191 Creating a Table with a Primary Key......Page 192 How It Works......Page 193 How It Works......Page 194 How It Works......Page 195 How It Works......Page 196 Allowing Cascading Changes in Foreign Keys......Page 197 How It Works......Page 198 Using the IDENTITY Property During Table Creation......Page 199 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values......Page 200 How It Works......Page 201 Constraints......Page 202 Creating a Unique Constraint......Page 203 Adding a UNIQUE Constraint to an Existing Table......Page 204 How It Works......Page 205 How It Works......Page 206 Disabling and Enabling a Constraint......Page 207 Using a DEFAULT Constraint During Table Creation......Page 208 How It Works......Page 209 Temporary Tables and Table Variables......Page 210 Using a Temporary Table for Multiple Lookups Within a Batch......Page 211 Creating a Table Variable to Hold a Temporary Result Set......Page 212 How It Works......Page 213 Manageability for Very Large Tables......Page 214 Implementing Table Partitioning......Page 215 How It Works......Page 217 Determining the Location of Data in a Partition......Page 218 How It Works......Page 219 Adding a New Partition......Page 220 How It Works......Page 221 How It Works......Page 222 Moving a Partition to a Different Table......Page 223 Removing Partition Functions and Schemes......Page 224 Easing VLDB Manageability with Filegroups......Page 225 Reducing Disk Space Usage with Data Compression......Page 226 How It Works......Page 229 Index Overview......Page 231 Creating a Table Index......Page 233 Enforcing Uniqueness on Non-Key Columns......Page 235 How It Works......Page 236 Viewing Index Meta Data......Page 237 How It Works......Page 238 How It Works......Page 239 Changing an Existing Index with DROP_EXISTING......Page 240 How It Works......Page 241 Allowing User Table Access During Index Creation......Page 242 Using an Index INCLUDE......Page 243 How It Works......Page 244 How It Works......Page 245 Creating an Index on a Filegroup......Page 246 How It Works......Page 247 How It Works......Page 248 How It Works......Page 249 Creating a Full-Text Catalog......Page 251 How It Works......Page 252 Creating a Full-Text Index......Page 253 How It Works......Page 254 Modifying a Full-Text Catalog......Page 255 Modifying a Full-Text Index......Page 256 How It Works......Page 258 Retrieving Full-Text Catalog and Index Metadata......Page 259 Discarding Common Strings from a Full-Text Index......Page 260 Dropping a Full-Text Index......Page 263 Basic Searching......Page 264 How It Works......Page 265 Advanced Searching......Page 266 Using CONTAINS to Search for Inflectional Matches......Page 267 How It Works......Page 268 Returning Ranked Search Results by Meaning......Page 269 Returning Ranked Search Results by Weighted Value......Page 270 How It Works......Page 271 Views......Page 273 Creating a Basic View......Page 274 Querying the View Definition......Page 276 Displaying Views and Their Structures......Page 277 Refreshing a View’s Definition......Page 278 Dropping a View......Page 279 Modifying Data Through a View......Page 280 How It Works......Page 281 Creating an Indexed View......Page 282 How It Works......Page 284 Partitioned Views......Page 285 Creating a Distributed-Partitioned View......Page 286 How It Works......Page 289 Aggregate Functions......Page 291 How It Works......Page 292 Finding the Lowest and Highest Values from an Expression......Page 293 Using Statistical Aggregate Functions......Page 294 Mathematical Functions......Page 295 Performing Mathematical Operations......Page 296 String Functions......Page 297 Converting a Character Value to ASCII and Back to Character......Page 298 How It Works......Page 299 Finding the Start Position of a String Within Another String Using Wildcards......Page 300 How It Works......Page 301 How It Works......Page 302 Replacing a Part of a String......Page 303 Changing Between Lowerand Uppercase......Page 304 Removing Leading and Trailing Blanks......Page 305 Number of Times......Page 306 Returning a Chunk of an Expression......Page 307 Replacing a NULL Value with an Alternative Value......Page 308 Performing Flexible Searches Using ISNULL......Page 309 Returning the First Non-NULL Value in a List of Expressions......Page 310 Date Functions......Page 311 Returning the Current Date and Time......Page 312 How It Works......Page 313 Incrementing or Decrementing a Date’s Value......Page 314 Finding the Difference Between Two Dates......Page 315 Displaying the Integer Representation for Parts of a Date......Page 316 Displaying the Integer Value for Part of a Date Using YEAR, MONTH, and DAY......Page 317 Converting Between Data Types......Page 318 Converting Dates to Their Textual Representation......Page 319 Representing Binary Data in String Literals......Page 320 Evaluating the Data Type Returned by an Expression......Page 321 Ranking Functions......Page 322 Generating an Incrementing Row Number......Page 323 Returning Rows by Rank......Page 324 How It Works......Page 325 Using NTILE......Page 326 Determining the First Day of the Week......Page 327 How It Works......Page 328 Displaying the Nesting Level for the Current Stored Procedure Context......Page 329 How It Works......Page 330 Retrieving the Number of Rows Affected by the Previous Statement......Page 331 Retrieving System Statistics......Page 332 Displaying Database and SQL Server Settings......Page 333 How It Works......Page 334 Returning the Application and Host for the Current User Session......Page 335 Reporting Current User and Login Context......Page 336 IDENTITY and uniqueidentifier Functions......Page 337 How It Works......Page 338 Creating a New uniqueidentifier Value......Page 339 How It Works......Page 340 Conditional Processing......Page 341 Using CASE to Evaluate a Single Input Expression......Page 342 Using CASE to Evaluate Boolean Expressions......Page 343 Using IF...ELSE......Page 344 How It Works......Page 345 Control-of-Flow......Page 346 Using RETURN......Page 347 Using WHILE......Page 348 Using GOTO......Page 350 How It Works......Page 351 Using WAITFOR......Page 352 Cursors......Page 353 Creating and Using Transact-SQL Cursors......Page 355 How It Works......Page 356 Stored Procedure Basics......Page 359 Creating a Basic Stored Procedure......Page 360 How It Works......Page 361 Creating a Parameterized Stored Procedure......Page 362 How It Works......Page 363 Using OUTPUT Parameters......Page 364 How It Works......Page 365 Dropping Stored Procedures......Page 366 Executing Stored Procedures Automatically at SQL Server Startup......Page 367 How It Works......Page 368 Stored Procedure Security......Page 369 How It Works......Page 370 Using EXECUTE AS to Specify the Procedure’s Security Context......Page 371 Recompilation and Caching......Page 374 How It Works......Page 375 How It Works......Page 376 UDF Basics......Page 377 Creating Scalar User-Defined Functions......Page 378 How It Works......Page 381 Creating Inline User-Defined Functions......Page 383 How It Works......Page 384 Creating Multi-Statement User-Defined Functions......Page 385 How It Works......Page 387 Modifying User-Defined Functions......Page 388 How It Works......Page 389 Dropping User-Defined Functions......Page 390 Maintaining Reusable Code......Page 391 Cross-Referencing Natural Key Values......Page 393 How It Works......Page 395 Replacing Views with Multi-Statement UDFs......Page 396 How It Works......Page 398 Creating and Using User-Defined Types......Page 399 Identifying Columns and Parameters with Dependencies on User-Defined Types......Page 401 Dropping User-Defined Types......Page 402 Passing Table-Valued Parameters......Page 403 How It Works......Page 405 Triggers......Page 407 DML Triggers......Page 408 Creating an AFTER DML Trigger......Page 409 How It Works......Page 411 Creating an INSTEAD OF DML Trigger......Page 412 How It Works......Page 414 Handling Transactions Within DML Triggers......Page 415 How It Works......Page 417 How It Works......Page 418 How It Works......Page 419 DDL Triggers......Page 420 Creating a DDL Trigger That Audits Database-Level Events......Page 421 How It Works......Page 422 How It Works......Page 423 Using a Logon Trigger......Page 424 How It Works......Page 425 Viewing DDL Trigger Metadata......Page 426 Modifying a Trigger......Page 427 Enabling and Disabling Table Triggers......Page 428 Limiting Trigger Nesting......Page 429 Controlling Trigger Recursion......Page 430 Setting Trigger Firing Order......Page 431 Dropping a Trigger......Page 433 How It Works......Page 434 CLR Integration......Page 435 When (and When Not) to Use Assemblies......Page 436 Creating CLR Database Objects......Page 438 Writing an Assembly for a CLR Stored Procedure......Page 439 How It Works......Page 440 Compiling an Assembly into a DLL File......Page 442 Loading the Assembly into SQL Server......Page 443 Creating the CLR Stored Procedure......Page 444 How It Works......Page 445 Creating a CLR Scalar User-Defined Function......Page 446 How It Works......Page 447 Creating a CLR Trigger......Page 449 How It Works......Page 450 Modifying an Assembly’s Permissions......Page 451 How It Works......Page 452 Creating XML Data Type Columns......Page 453 How It Works......Page 454 Inserting XML Data into a Column......Page 455 Validating XML Data Using Schemas......Page 456 Retrieving XML Data......Page 458 Modifying XML Data......Page 461 Indexing XML Data......Page 462 How It Works......Page 463 Formatting Relational Data As XML......Page 464 Converting XML to a Relational Form......Page 467 Storing Hierarchical Data......Page 469 How It Works......Page 472 How It Works......Page 473 How It Works......Page 474 How It Works......Page 475 Storing Spatial Data......Page 476 How It Works......Page 478 Querying Spatial Data......Page 479 How It Works......Page 481 Using Join Hints......Page 483 Forcing a HASH Join......Page 484 Using Query Hints......Page 485 Forcing a Statement Recompile......Page 486 Using Table Hints......Page 488 Forcing a SEEK over a SCAN......Page 490 How It Works......Page 492 Viewing System Error Information......Page 493 Creating a User-Defined Error Message......Page 494 How It Works......Page 495 Manually Raising an Error......Page 496 Invoking an Error Message......Page 497 How It Works......Page 498 Trapping and Handling Application Errors......Page 499 Old-Style Error Handling......Page 500 How It Works......Page 501 Error Handling with TRY...CATCH......Page 502 How It Works......Page 503 How It Works......Page 504 Nesting Error Handling......Page 505 How It Works......Page 506 Windows Principals......Page 509 Creating a Windows Login......Page 510 Viewing Windows Logins......Page 511 Altering a Windows Login......Page 512 How It Works......Page 513 SQL Server Principals......Page 514 Viewing SQL Server Logins......Page 516 Altering a SQL Server Login......Page 517 Managing a Login’s Password......Page 518 Managing Server Role Members......Page 519 Reporting Fixed Server Role Information......Page 520 Database Principals......Page 522 Creating Database Users......Page 523 Modifying a Database User......Page 524 Fixing Orphaned Database Users......Page 525 How It Works......Page 526 How It Works......Page 527 How It Works......Page 528 Managing User-Defined Database Roles......Page 529 How It Works......Page 530 Managing Application Roles......Page 531 How It Works......Page 533 Securables, Permissions, and Auditing......Page 535 Permissions Overview......Page 536 Reporting SQL Server Assignable Permissions......Page 537 How It Works......Page 538 Server-Scoped Securables and Permissions......Page 539 Managing Server Permissions......Page 541 Querying Server-Level Permissions......Page 542 Database-Scoped Securables and Permissions......Page 543 Managing Database Permissions......Page 544 Querying Database Permissions......Page 545 How It Works......Page 547 Schema-Scoped Securables and Permissions......Page 548 Managing Schemas......Page 550 Managing Schema Permissions......Page 551 Object Permissions......Page 553 Managing Object Permissions......Page 555 Determining a Current Connection’s Permissions to a Securable......Page 556 Reporting the Permissions for a Principal by Securable Scope......Page 557 How It Works......Page 560 Changing Securable Ownership......Page 561 Allowing SQL Logins to Access Non-SQL Server Resources......Page 562 Auditing SQL Instance and Database-Level Activity of Principals Against Securables......Page 563 Defining Audit Data Sources......Page 564 How It Works......Page 566 Capturing SQL Instance–Scoped Events......Page 567 Capturing Database-Scoped Events......Page 569 How It Works......Page 572 Querying Captured Audit Data......Page 573 How It Works......Page 576 Managing, Modifying, and Removing Audit Objects......Page 577 How It Works......Page 579 Encryption by Passphrase......Page 581 Using a Function to Encrypt by Passphrase......Page 582 How It Works......Page 583 Backing Up and Restoring a Service Master Key......Page 584 Creating, Regenerating, and Dropping a Database Master Key......Page 585 How It Works......Page 586 How It Works......Page 587 Removing Service Master Key Encryption from the Database Master Key......Page 588 Creating an Asymmetric Key......Page 589 Viewing Asymmetric Keys in the Current Database......Page 590 Encrypting and Decrypting Data Using an Asymmetric Key......Page 591 How It Works......Page 593 Creating a Symmetric Key......Page 594 How It Works......Page 595 How It Works......Page 596 Using Symmetric Key Encryption and Decryption......Page 597 How It Works......Page 600 Creating a Database Certificate......Page 601 Viewing Certificates in the Database......Page 602 Backing Up and Restoring a Certificate......Page 603 Managing a Certificate’s Private Key......Page 604 Using Certificate Encryption and Decryption......Page 605 Automatically Opening and Decrypting via a Symmetric Key......Page 607 How It Works......Page 608 Enabling Transparent Data Encryption......Page 609 Managing and Removing TDE......Page 610 How It Works......Page 612 Service Broker......Page 613 Creating a Basic Service Broker Application......Page 614 Enabling Databases for Service Broker Activity......Page 615 Managing Message Types......Page 616 How It Works......Page 617 Creating Contracts......Page 618 Creating Queues......Page 619 Creating Services......Page 621 How It Works......Page 622 Initiating a Dialog......Page 623 How It Works......Page 624 Receiving and Responding to a Message......Page 625 How It Works......Page 627 Ending a Conversation......Page 628 How It Works......Page 629 Prioritizing Service Broker Conversations......Page 630 How It Works......Page 631 Creating the Bookstore Stored Procedure......Page 632 How It Works......Page 634 Remote-Server Service Broker Implementations......Page 635 Enabling Transport Security......Page 637 Enabling Dialog Security......Page 640 Creating Routes and Remote Service Bindings......Page 642 How It Works......Page 645 Capturing Login Commands......Page 646 How It Works......Page 648 Viewing SQL Server Configurations......Page 649 Unknown......Page 0 Changing SQL Server Configurations......Page 651 How It Works......Page 653 Creating, Altering, and Dropping Databases......Page 655 Viewing Database Information......Page 656 How It Works......Page 657 Creating a Database Using File Options......Page 658 How It Works......Page 659 Creating a Database with a User-Defined Filegroup......Page 661 Setting Database User Access......Page 662 How It Works......Page 664 How It Works......Page 665 Detaching a Database......Page 666 Attaching a Database......Page 668 Configuring Database Options......Page 669 Configuring ANSI SQL Options......Page 670 Configuring Automatic Options......Page 672 How It Works......Page 673 How It Works......Page 674 Creating or Changing a Database to Use a Non-Server Default Collation......Page 675 Configuring Cursor Options......Page 676 Enabling Date Correlation Optimization......Page 677 Modifying Database Parameterization Behavior......Page 678 How It Works......Page 680 Enabling Read Consistency for a Transaction......Page 681 How It Works......Page 682 Configuring Database Recovery Models......Page 683 Configuring Page Verification......Page 684 Controlling Database Access and Ownership......Page 685 How It Works......Page 686 How It Works......Page 687 Adding a Data File or Log File to an Existing Database......Page 688 How It Works......Page 689 How It Works......Page 690 Relocating a Data or Transaction Log File......Page 691 How It Works......Page 692 How It Works......Page 693 Setting the Default Filegroup......Page 694 Removing a Filegroup......Page 695 Making a Database or Filegroup Read-Only......Page 696 Viewing Database Space Usage......Page 697 Shrinking the Database or a Database File......Page 699 How It Works......Page 702 Database Integrity Checking......Page 703 Checking Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC......Page 704 How It Works......Page 705 Checking Allocation and Structural Integrity with DBCC CHECKDB......Page 706 Tables and Constraints......Page 708 Checking Allocation and Structural Integrity of All Tables in a Filegroup Using DBCC CHECKFILEGROUP......Page 709 Checking Data Integrity for Tables and Indexed Views Using DBCC CHECKTABLE......Page 710 Checking Table Integrity with DBCC CHECKCONSTRAINTS......Page 713 How It Works......Page 714 Checking System Table Consistency with DBCC CHECKCATALOG......Page 715 Rebuilding Indexes......Page 716 How It Works......Page 718 How It Works......Page 719 How It Works......Page 720 Changing the Name of a User-Created Database Object......Page 721 How It Works......Page 722 How It Works......Page 723 Identifying Object Dependencies......Page 724 Identifying Referencing and Referenced Entities......Page 726 How It Works......Page 727 Viewing an Object’s Definition......Page 728 How It Works......Page 729 Database Mirroring in Context......Page 731 Database Mirroring Architecture......Page 732 Creating Mirroring Endpoints......Page 734 How It Works......Page 738 Backing Up and Restoring Principal Databases......Page 739 How It Works......Page 740 Creating a Database Mirroring Session......Page 741 Setup Summary......Page 743 Operating Database Mirroring......Page 744 How It Works......Page 745 Performing Failovers......Page 746 How It Works......Page 747 Monitoring Mirror Status......Page 748 Configuring the Connection Timeout Period......Page 749 How It Works......Page 750 Snapshot Basics......Page 751 Creating and Querying Database Snapshots......Page 752 Removing a Database Snapshot......Page 753 Recovering Data with a Database Snapshot......Page 754 How It Works......Page 756 Linked Server Basics......Page 757 Creating a Linked Server to Another SQL Server Instance......Page 758 Configuring Linked Server Properties......Page 759 Dropping a Linked Server......Page 761 Adding a Linked Server Login Mapping......Page 762 How It Works......Page 763 Executing Distributed Queries Against a Linked Server......Page 764 How It Works......Page 765 How It Works......Page 766 Executing Ad Hoc Queries Using OPENROWSET......Page 767 How It Works......Page 768 Reading Data from a File Using OPENROWSET BULK Options......Page 769 How It Works......Page 771 Query Performance Tuning......Page 773 Query Performance Tips......Page 774 How It Works......Page 776 Viewing Estimated Query Execution Plans Using Transact-SQL Commands......Page 777 How It Works......Page 779 Viewing Execution Runtime Information......Page 780 Viewing Performance Statistics for Cached Query Plans......Page 782 How It Works......Page 783 Viewing Aggregated Performance Statistics Based on Query or Plan Patterns......Page 784 How It Works......Page 785 Identifying the Top Bottleneck......Page 786 Identifying I/O Contention by Database and File......Page 787 Index Tuning......Page 788 Displaying Index Fragmentation......Page 790 How It Works......Page 791 How It Works......Page 793 Manually Creating Statistics......Page 794 Creating Statistics on a Subset of Rows......Page 795 Updating Statistics......Page 796 Generating and Updating Statistics Across All Tables......Page 797 Viewing Statistics Details......Page 799 Miscellaneous Techniques......Page 800 Using an Alternative to Dynamic SQL......Page 801 How It Works......Page 802 Forcing SQL Server to Use a Query Plan......Page 803 Applying Hints Without Modifying Application SQL......Page 805 How It Works......Page 808 Creating Plan Guides from Cache......Page 809 Checking the Validity of a Plan Guide......Page 811 Parameterizing a Non-parameterized Query Using Plan Guides......Page 812 How It Works......Page 814 Limiting Competing Query Resource Consumption......Page 815 How It Works......Page 821 Creating a Backup and Recovery Plan......Page 823 Making Backups......Page 825 Performing a Basic Full Backup......Page 827 Compressing Your Backups......Page 828 Naming and Describing Your Backups and Media......Page 830 Configuring Backup Retention......Page 831 How It Works......Page 832 Using a Named Backup Device......Page 833 Mirroring Backup Sets......Page 835 How It Works......Page 836 Performing a Transaction Log Backup......Page 837 How It Works......Page 838 Backing Up Individual Files or Filegroups......Page 839 Performing a Partial Backup......Page 841 Viewing Backup Metadata......Page 842 How It Works......Page 843 Restoring a Database from a Full Backup......Page 844 Restoring a Database from a Transaction Log Backup......Page 846 Restoring a Database from a Differential Backup......Page 849 Restoring a File or Filegroup......Page 850 How It Works......Page 851 Performing a Piecemeal (PARTIAL) Restore......Page 852 Restoring a Page......Page 853 Identifying Databases with Multiple Recovery Paths......Page 854 How It Works......Page 856 Index......Page 857 SQL Server 2008 Transact-SQL Recipes 1 Contents at a Glance 6 Contents 9 About the Author 27 About the Technical Reviewer 29 Acknowledgments 31 Introduction 33 SELECT 35 The Basic SELECT Statement 35 Selecting Specific Columns from a Table 36 How It Works 36 Selecting Every Column for Every Row 37 How It Works 37 Selective Querying Using a Basic WHERE Clause 37 Using the WHERE Clause to Specify Rows Returned in the Result Set 38 How It Works 38 Combining Search Conditions 38 How It Works 39 Negating a Search Condition 40 How It Works 40 Keeping Your WHERE Clause Unambiguous 40 How It Works 41 Using Operators and Expressions 41 Using BETWEEN for Date Range Searches 43 How It Works 43 Using Comparisons 43 How It Works 44 Checking for NULL Values 44 How It Works 44 Returning Rows Based on a List of Values 45 How It Works 45 Using Wildcards with LIKE 45 How It Works 46 Declaring and Assigning Values to Variables 46 How It Works 47 Grouping Data 48 Using the GROUP BY Clause 48 How It Works 48 Using GROUP BY ALL 49 How It Works 50 Selectively Querying Grouped Data Using HAVING 50 How It Works 51 Ordering Results 51 Using the ORDER BY Clause 51 How It Works 52 Using the TOP Keyword with Ordered Results 53 How It Works 54 SELECT Clause Techniques 55 Using DISTINCT to Remove Duplicate Values 55 How It Works 56 Using DISTINCT in Aggregate Functions 56 How It Works 56 Using Column Aliases 56 How It Works 57 Using SELECT to Create a Script 57 How It Works 58 Performing String Concatenation 58 How It Works 59 Creating a Comma-Delimited List Using SELECT 59 How It Works 59 Using the INTO Clause 60 How It Works 61 Subqueries 61 Using Subqueries to Check for Matches 61 How It Works 62 Querying from More Than One Data Source 62 Using INNER Joins 63 How It Works 63 Using OUTER Joins 64 How It Works 65 Using CROSS Joins 65 How It Works 66 Referencing a Single Table Multiple Times in the Same Query 66 How It Works 66 Using Derived Tables 67 How It Works 67 Combining Result Sets with UNION 67 How It Works 68 Using APPLY to Invoke a Table-Valued Function for Each Row 69 Using CROSS APPLY 69 How It Works 70 Using OUTER APPLY 71 How It Works 72 Advanced Techniques for Data Sources 72 Using the TABLESAMPLE to Return Random Rows 72 How It Works 73 Using PIVOT to Convert Single Column Values into Multiple Columns and Aggregate Data 73 How It Works 75 Normalizing Data with UNPIVOT 76 How It Works 77 Returning Distinct or Matching Rows Using EXCEPT and INTERSECT 78 How It Works 79 Summarizing Data 80 Summarizing Data Using CUBE 80 How It Works 82 Summarizing Data Using ROLLUP 82 How It Works 83 Creating Custom Summaries Using Grouping Sets 83 How It Works 85 Revealing Rows Generated by GROUPING 85 How It Works 86 Advanced Group-Level Identification with GROUPING_ID 87 How It Works 89 Common Table Expressions 90 Using a Non-Recursive Common Table Expression 90 How It Works 92 Using a Recursive Common Table Expression 93 How It Works 94 Perform, Capture, and Track Data Modifications 97 INSERT 97 Inserting a Row into a Table 98 How It Works 98 Inserting a Row Using Default Values 99 How It Works 100 Explicitly Inserting a Value into an IDENTITY Column 100 How It Works 101 Inserting a Row into a Table with a uniqueidentifier Column 101 How It Works 102 Inserting Rows Using an INSERT...SELECT Statement 102 How It Works 103 Inserting Data from a Stored Procedure Call 104 How It Works 105 Inserting Multiple Rows with VALUES 105 How It Works 106 Using VALUES As a Table Source 106 How It Works 107 UPDATE 107 Updating a Single Row 108 How It Works 108 Updating Rows Based on a FROM and WHERE Clause 109 How It Works 109 Updating Large Value Data Type Columns 110 How It Works 111 Inserting or Updating an Image File Using OPENROWSET and BULK 112 How It Works 113 Storing Unstructured Data on the File System While Maintaining SQL Server Transactional Control 114 How It Works 117 Assigning and Modifying Database Values “in Place” 118 How It Works 120 DELETE 120 Deleting Rows 120 How It Works 121 Truncating a Table 122 How It Works 122 Advanced Data Modification Techniques 123 Chunking Data Modifications with TOP 123 How It Works 124 Executing INSERTs, UPDATEs, and DELETEs in a Single Statement 124 How It Works 126 Capturing and Tracking Data Modification Changes 127 Returning Rows Affected by a Data Modification Statement 127 How It Works 129 Asynchronously Capturing Table Data Modifications 130 How It Works 133 Querying All Changes from CDC Tables 133 How It Works 136 Querying Net Changes from CDC Tables 137 How It Works 138 Translating the CDC Update Mask 138 How It Works 139 Working with LSN Boundaries 139 How It Works 140 Disabling Change Data Capture from Tables and the Database 141 How It Works 141 Tracking Net Data Changes with Minimal Disk Overhead 141 How It Works 147 Transactions, Locking, Blocking, and Deadlocking 149 Transaction Control 149 Using Explicit Transactions 151 How It Works 152 Displaying the Oldest Active Transaction with DBCC OPENTRAN 153 How It Works 154 Querying Transaction Information by Session 154 How It Works 156 Locking 156 Viewing Lock Activity 158 How It Works 159 Controlling a Table’s Lock Escalation Behavior 160 How It Works 161 Transaction, Locking, and Concurrency 162 Configuring a Session’s Transaction Locking Behavior 163 How It Works 166 Blocking 168 Identifying and Resolving Blocking Issues 168 How It Works 170 Configuring How Long a Statement Will Wait for a Lock to Be Released 170 How It Works 171 Deadlocking 171 Identifying Deadlocks with a Trace Flag 172 How It Works 175 Setting Deadlock Priority 175 How It Works 176 Tables 177 Table Basics 177 Creating a Table 181 How It Works 181 Adding a Column to an Existing Table 181 How It Works 182 Changing an Existing Column Definition 182 How It Works 183 Creating a Computed Column 183 How It Works 184 Reducing Storage for Null Columns 184 How It Works 187 Dropping a Table Column 187 How It Works 188 Reporting Table Information 188 How It Works 188 Dropping a Table 188 How It Works 189 Collation Basics 189 Viewing Collation Metadata 189 How It Works 190 Designating a Column’s Collation 190 How It Works 191 Keys 191 Creating a Table with a Primary Key 192 How It Works 193 Adding a Primary Key Constraint to an Existing Table 193 How It Works 193 Creating a Table with a Foreign Key Reference 194 How It Works 194 Adding a Foreign Key to an Existing Table 195 How It Works 195 Creating Recursive Foreign Key References 196 How It Works 196 Allowing Cascading Changes in Foreign Keys 197 How It Works 198 Surrogate Keys 199 Using the IDENTITY Property During Table Creation 199 How It Works 200 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values 200 How It Works 201 Using the ROWGUIDCOL Property 202 How It Works 202 Constraints 202 Creating a Unique Constraint 203 How It Works 204 Adding a UNIQUE Constraint to an Existing Table 204 How It Works 205 Using CHECK Constraints 205 How It Works 205 Adding a CHECK Constraint to an Existing Table 206 How It Works 206 Disabling and Enabling a Constraint 207 How It Works 208 Using a DEFAULT Constraint During Table Creation 208 How It Works 209 Adding a DEFAULT Constraint to an Existing Table 209 How It Works 209 Dropping a Constraint from a Table 210 How It Works 210 Temporary Tables and Table Variables 210 Using a Temporary Table for Multiple Lookups Within a Batch 211 How It Works 212 Creating a Table Variable to Hold a Temporary Result Set 212 How It Works 213 Manageability for Very Large Tables 214 Implementing Table Partitioning 215 How It Works 217 Determining the Location of Data in a Partition 218 How It Works 219 Adding a New Partition 220 How It Works 221 Removing a Partition 222 How It Works 222 Moving a Partition to a Different Table 223 How It Works 224 Removing Partition Functions and Schemes 224 How It Works 225 Easing VLDB Manageability with Filegroups 225 How It Works 226 Reducing Disk Space Usage with Data Compression 226 How It Works 229 Indexes 231 Index Overview 231 Creating a Table Index 233 How It Works 235 Enforcing Uniqueness on Non-Key Columns 235 How It Works 236 Creating an Index on Multiple Columns 236 How It Works 236 Defining Index Column Sort Direction 237 How It Works 237 Viewing Index Meta Data 237 How It Works 238 Disabling an Index 239 How It Works 239 Dropping Indexes 240 How It Works 240 Changing an Existing Index with DROP_EXISTING 240 How It Works 241 Controlling Index Build Performance and Concurrency 241 Intermediate Index Creation in Tempdb 241 How It Works 241 Controlling Parallel Plan Execution for Index Creation 242 How It Works 242 Allowing User Table Access During Index Creation 242 How It Works 243 Index Options 243 Using an Index INCLUDE 243 How It Works 244 Using PAD_INDEX and FILLFACTOR 244 How It Works 244 Disabling Page and/or Row Index Locking 245 How It Works 245 Managing Very Large Indexes 246 Creating an Index on a Filegroup 246 How It Works 247 Implementing Index Partitioning 247 How It Works 247 Indexing a Subset of Rows 248 How It Works 248 Reducing Index Size 249 How It Works 249 Full-Text Search 251 Full-Text Indexes and Catalogs 251 Creating a Full-Text Catalog 251 How It Works 252 Creating a Full-Text Index 253 How It Works 254 Modifying a Full-Text Catalog 255 How It Works 256 Modifying a Full-Text Index 256 How It Works 258 Retrieving Full-Text Catalog and Index Metadata 259 How It Works 260 Discarding Common Strings from a Full-Text Index 260 How It Works 263 Dropping a Full-Text Index 263 How It Works 264 Dropping a Full-Text Catalog 264 How It Works 264 Basic Searching 264 Using FREETEXT to Search Full-Text Indexed Columns 265 How It Works 265 Using CONTAINS for Word Searching 266 How It Works 266 Advanced Searching 266 Using CONTAINS to Search with Wildcards 267 How It Works 267 Using CONTAINS to Search for Inflectional Matches 267 How It Works 268 Using CONTAINS for Searching Results by Term Proximity 268 How It Works 268 Ranked Searching 269 Returning Ranked Search Results by Meaning 269 How It Works 270 Returning Ranked Search Results by Weighted Value 270 How It Works 271 Views 273 Regular Views 274 Creating a Basic View 274 How It Works 276 Querying the View Definition 276 How It Works 277 Displaying Views and Their Structures 277 How It Works 278 Refreshing a View’s Definition 278 How It Works 279 Modifying a View 279 How It Works 279 Dropping a View 279 How It Works 280 Modifying Data Through a View 280 How It Works 281 View Encryption 281 Encrypting a View 281 How It Works 281 Indexed Views 282 Creating an Indexed View 282 How It Works 284 Forcing the Optimizer to Use an Index for an Indexed View 285 How It Works 285 Partitioned Views 285 Creating a Distributed-Partitioned View 286 How It Works 289 SQL Server Functions 291 Aggregate Functions 291 Returning the Average of Values 292 How It Works 292 Returning Row Counts 293 How It Works 293 Finding the Lowest and Highest Values from an Expression 293 How It Works 294 Returning the Sum of Values 294 How It Works 294 Using Statistical Aggregate Functions 294 How It Works 295 Mathematical Functions 295 Performing Mathematical Operations 296 How It Works 297 String Functions 297 Converting a Character Value to ASCII and Back to Character 298 How It Works 299 Returning Integer and Character Unicode Values 299 How It Works 299 Finding the Start Position of a String Within Another String 300 How It Works 300 Finding the Start Position of a String Within Another String Using Wildcards 300 How It Works 301 Determining the Similarity of Strings 301 How It Works 301 Taking the Leftmost or Rightmost Part of a String 302 How It Works 302 Determining the Number of Characters or Bytes in a String 303 How It Works 303 Replacing a Part of a String 303 How It Works 304 Stuffing a String into a String 304 How It Works 304 Changing Between Lowerand Uppercase 304 How It Works 305 Removing Leading and Trailing Blanks 305 How It Works 306 Repeating an Expression 306 Number of Times 306 How It Works 306 Repeating a Blank Space 306 Number of Times 306 How It Works 307 Outputting an Expression in Reverse Order 307 How It Works 307 Returning a Chunk of an Expression 307 How It Works 308 Working with NULLs 308 Replacing a NULL Value with an Alternative Value 308 How It Works 309 Performing Flexible Searches Using ISNULL 309 How It Works 310 Returning the First Non-NULL Value in a List of Expressions 310 How It Works 311 Returning a NULL Value When Two Expressions Are Equal: Otherwise Returning the First Expression 311 How It Works 311 Date Functions 311 Returning the Current Date and Time 312 How It Works 313 Converting Between Time Zones 313 How It Works 313 Incrementing or Decrementing a Date’s Value 314 How It Works 315 Finding the Difference Between Two Dates 315 How It Works 316 Displaying the String Value for Part of a Date 316 How It Works 316 Displaying the Integer Representation for Parts of a Date 316 How It Works 317 Displaying the Integer Value for Part of a Date Using YEAR, MONTH, and DAY 317 How It Works 318 Type Conversion 318 Converting Between Data Types 318 How It Works 319 Converting Dates to Their Textual Representation 319 How It Works 320 Representing Binary Data in String Literals 320 How It Works 321 Evaluating the Data Type Returned by an Expression 321 How It Works 322 Ranking Functions 322 Generating an Incrementing Row Number 323 How It Works 324 Returning Rows by Rank 324 How It Works 325 Returning Rows by Rank Without Gaps 326 How It Works 326 Using NTILE 326 How It Works 327 Probing Server, Database, and Connection-Level Settings Using System Functions 327 Determining the First Day of the Week 327 How It Works 328 Viewing the Language Used in the Current Session 328 How It Works 328 Viewing and Setting Current Connection Lock Timeout Settings 329 How It Works 329 Displaying the Nesting Level for the Current Stored Procedure Context 329 How It Works 330 Returning the Current SQL Server Instance Name and SQL Server Version 330 How It Works 330 Returning the Current Connection’s Session ID (SPID) 330 How It Works 330 Returning the Number of Open Transactions 331 How It Works 331 Retrieving the Number of Rows Affected by the Previous Statement 331 How It Works 332 Retrieving System Statistics 332 How It Works 333 Displaying Database and SQL Server Settings 333 How It Works 334 Returning the Current Database ID and Name 334 How It Works 334 Returning a Database Object Name and ID 335 How It Works 335 Returning the Application and Host for the Current User Session 335 How It Works 336 Reporting Current User and Login Context 336 How It Works 337 Viewing User Connection Options 337 How It Works 337 IDENTITY and uniqueidentifier Functions 337 Returning the Last Identity Value 338 How It Works 338 Returning an Identity Column’s Seed and Incrementing Value 339 How It Works 339 Creating a New uniqueidentifier Value 339 How It Works 340 Conditional Processing, Control-ofFlow, and Cursors 341 Conditional Processing 341 Using CASE to Evaluate a Single Input Expression 342 How It Works 343 Using CASE to Evaluate Boolean Expressions 343 How It Works 344 Using IF...ELSE 344 How It Works 345 Control-of-Flow 346 Using RETURN 347 How It Works 348 Using WHILE 348 How It Works 350 Using GOTO 350 How It Works 351 Using WAITFOR 352 How It Works 353 Cursors 353 Creating and Using Transact-SQL Cursors 355 How It Works 356 Stored Procedures 359 Stored Procedure Basics 359 Creating a Basic Stored Procedure 360 How It Works 361 Creating a Parameterized Stored Procedure 362 How It Works 363 Using OUTPUT Parameters 364 How It Works 365 Modifying a Stored Procedure 366 How It Works 366 Dropping Stored Procedures 366 How It Works 367 Executing Stored Procedures Automatically at SQL Server Startup 367 How It Works 368 Reporting Stored Procedure Metadata 368 How It Works 368 Documenting Stored Procedures 369 How It Works 369 Stored Procedure Security 369 Encrypting a Stored Procedure 370 How It Works 370 Using EXECUTE AS to Specify the Procedure’s Security Context 371 How It Works 374 Recompilation and Caching 374 RECOMPILE(ing) a Stored Procedure Each Time It Is Executed 375 How It Works 375 Flushing the Procedure Cache 376 How It Works 376 User-Defined Functions and Types 377 UDF Basics 377 Creating Scalar User-Defined Functions 378 How It Works 381 Creating Inline User-Defined Functions 383 How It Works 384 Creating Multi-Statement User-Defined Functions 385 How It Works 387 Modifying User-Defined Functions 388 How It Works 389 Viewing UDF Metadata 390 How It Works 390 Dropping User-Defined Functions 390 How It Works 391 Benefitting from UDFs 391 Maintaining Reusable Code 391 How It Works 393 Cross-Referencing Natural Key Values 393 How It Works 395 Replacing Views with Multi-Statement UDFs 396 How It Works 398 UDT Basics 399 Creating and Using User-Defined Types 399 How It Works 401 Identifying Columns and Parameters with Dependencies on User-Defined Types 401 How It Works 402 Dropping User-Defined Types 402 How It Works 403 Passing Table-Valued Parameters 403 How It Works 405 Triggers 407 DML Triggers 408 Creating an AFTER DML Trigger 409 How It Works 411 Creating an INSTEAD OF DML Trigger 412 How It Works 414 Handling Transactions Within DML Triggers 415 How It Works 417 Controlling DML Triggers Based on Modified Columns 418 How It Works 418 Viewing DML Trigger Metadata 419 How It Works 419 DDL Triggers 420 Creating a DDL Trigger That Audits Database-Level Events 421 How It Works 422 Creating a DDL Trigger That Audits Server-Level Events 423 How It Works 423 Using a Logon Trigger 424 How It Works 425 Viewing DDL Trigger Metadata 426 How It Works 427 Managing Triggers 427 Modifying a Trigger 427 How It Works 428 Enabling and Disabling Table Triggers 428 How It Works 429 Limiting Trigger Nesting 429 How It Works 430 Controlling Trigger Recursion 430 How It Works 431 Setting Trigger Firing Order 431 How It Works 433 Dropping a Trigger 433 How It Works 434 CLR Integration 435 CLR Overview 436 When (and When Not) to Use Assemblies 436 CLR Objects Overview 438 Creating CLR Database Objects 438 Enabling CLR Support in SQL Server 439 How It Works 439 Writing an Assembly for a CLR Stored Procedure 439 How It Works 440 Compiling an Assembly into a DLL File 442 How It Works 443 Loading the Assembly into SQL Server 443 How It Works 444 Creating the CLR Stored Procedure 444 How It Works 445 Creating a CLR Scalar User-Defined Function 446 How It Works 447 Creating a CLR Trigger 449 How It Works 450 Administering Assemblies 451 Viewing Assembly Metadata 451 How It Works 451 Modifying an Assembly’s Permissions 451 How It Works 452 Removing an Assembly from the Database 452 How It Works 452 XML, Hierarchies, and Spatial Data 453 Working with Native XML 453 Creating XML Data Type Columns 453 How It Works 454 Inserting XML Data into a Column 455 How It Works 456 Validating XML Data Using Schemas 456 How It Works 458 Retrieving XML Data 458 How It Works 461 Modifying XML Data 461 How It Works 462 Indexing XML Data 462 How It Works 463 Converting Between XML Documents and Relational Data 464 Formatting Relational Data As XML 464 How It Works 467 Converting XML to a Relational Form 467 How It Works 469 Working with Native Hierarchical Data 469 Storing Hierarchical Data 469 How It Works 472 Returning a Specific Ancestor 472 How It Works 472 Returning Child Nodes 473 How It Works 473 Returning a Node’s Depth 474 How It Works 474 Returning the Root Node 474 How It Works 474 Determining Whether a Node Is a Child of the Current Node 475 How It Works 475 Changing Node Locations 475 How It Works 475 Native Spatial Data 476 Storing Spatial Data 476 How It Works 478 Querying Spatial Data 479 How It Works 481 Hints 483 Using Join Hints 483 Forcing a HASH Join 484 How It Works 485 Using Query Hints 485 Forcing a Statement Recompile 486 How It Works 488 Using Table Hints 488 Executing a Query Without Locking 490 How It Works 490 Forcing a SEEK over a SCAN 490 How It Works 492 Error Handling 493 System-Defined and User-Defined Error Messages 493 Viewing System Error Information 493 How It Works 494 Creating a User-Defined Error Message 494 How It Works 495 Dropping a User-Defined Error Message 496 How It Works 496 Manually Raising an Error 496 Invoking an Error Message 497 How It Works 498 Trapping and Handling Application Errors 499 Old-Style Error Handling 500 How It Works 501 Error Handling with TRY...CATCH 502 How It Works 503 Applying Error Handling Without Recoding a Stored Procedure 504 How It Works 504 Nesting Error Handling 505 How It Works 506 Principals 509 Windows Principals 509 Creating a Windows Login 510 How It Works 511 Viewing Windows Logins 511 How It Works 512 Altering a Windows Login 512 How It Works 513 Dropping a Windows Login 513 How It Works 513 Denying SQL Server Access to a Windows User or Group 514 How It Works 514 SQL Server Principals 514 Creating a SQL Server Login 516 How It Works 516 Viewing SQL Server Logins 516 How It Works 517 Altering a SQL Server Login 517 How It Works 518 Managing a Login’s Password 518 How It Works 519 Dropping a SQL Login 519 How It Works 519 Managing Server Role Members 519 How It Works 520 Reporting Fixed Server Role Information 520 How It Works 522 Database Principals 522 Creating Database Users 523 How It Works 524 Reporting Database User Information 524 How It Works 524 Modifying a Database User 524 How It Works 525 Removing a Database User from the Database 525 How It Works 525 Fixing Orphaned Database Users 525 How It Works 526 Reporting Fixed Database Roles Information 527 How It Works 527 Managing Fixed Database Role Membership 528 How It Works 528 Managing User-Defined Database Roles 529 How It Works 530 Managing Application Roles 531 How It Works 533 Securables, Permissions, and Auditing 535 Permissions Overview 536 Reporting SQL Server Assignable Permissions 537 How It Works 538 Server-Scoped Securables and Permissions 539 Managing Server Permissions 541 How It Works 542 Querying Server-Level Permissions 542 How It Works 543 Database-Scoped Securables and Permissions 543 Managing Database Permissions 544 How It Works 545 Querying Database Permissions 545 How It Works 547 Schema-Scoped Securables and Permissions 548 Managing Schemas 550 How It Works 551 Managing Schema Permissions 551 How It Works 553 Object Permissions 553 Managing Object Permissions 555 How It Works 556 Managing Permissions Across Securable Scopes 556 Determining a Current Connection’s Permissions to a Securable 556 How It Works 557 Reporting the Permissions for a Principal by Securable Scope 557 How It Works 560 Changing Securable Ownership 561 How It Works 562 Allowing SQL Logins to Access Non-SQL Server Resources 562 How It Works 563 Auditing SQL Instance and Database-Level Activity of Principals Against Securables 563 Defining Audit Data Sources 564 How It Works 566 Capturing SQL Instance–Scoped Events 567 How It Works 569 Capturing Database-Scoped Events 569 How It Works 572 Querying Captured Audit Data 573 How It Works 576 Managing, Modifying, and Removing Audit Objects 577 How It Works 579 Encryption 581 Encryption by Passphrase 581 Using a Function to Encrypt by Passphrase 582 How It Works 583 Master Keys 584 Backing Up and Restoring a Service Master Key 584 How It Works 585 Creating, Regenerating, and Dropping a Database Master Key 585 How It Works 586 Backing Up and Restoring a Database Master Key 587 How It Works 587 Removing Service Master Key Encryption from the Database Master Key 588 How It Works 589 Asymmetric Key Encryption 589 Creating an Asymmetric Key 589 How It Works 590 Viewing Asymmetric Keys in the Current Database 590 How It Works 591 Modifying the Asymmetric Key’s Private Key Password 591 How It Works 591 Encrypting and Decrypting Data Using an Asymmetric Key 591 How It Works 593 Dropping an Asymmetric Key 594 How It Works 594 Symmetric Key Encryption 594 Creating a Symmetric Key 594 How It Works 595 Viewing Symmetric Keys in the Current Database 596 How It Works 596 Changing How a Symmetric Key Is Encrypted 596 How It Works 596 Using Symmetric Key Encryption and Decryption 597 How It Works 600 Dropping a Symmetric Key 601 How It Works 601 Certificate Encryption 601 Creating a Database Certificate 601 How It Works 602 Viewing Certificates in the Database 602 How It Works 603 Backing Up and Restoring a Certificate 603 How It Works 604 Managing a Certificate’s Private Key 604 How It Works 605 Using Certificate Encryption and Decryption 605 How It Works 607 Automatically Opening and Decrypting via a Symmetric Key 607 How It Works 608 Transparent Data Encryption 609 Enabling Transparent Data Encryption 609 How It Works 610 Managing and Removing TDE 610 How It Works 612 Service Broker 613 Example Scenario: Online Bookstore 614 Creating a Basic Service Broker Application 614 Enabling Databases for Service Broker Activity 615 How It Works 616 Creating the Database Master Key for Encryption 616 How It Works 616 Managing Message Types 616 How It Works 617 Creating Contracts 618 How It Works 619 Creating Queues 619 How It Works 621 Creating Services 621 How It Works 622 Initiating a Dialog 623 How It Works 624 Querying the Queue for Incoming Messages 625 How It Works 625 Receiving and Responding to a Message 625 How It Works 627 Ending a Conversation 628 How It Works 629 Prioritizing Service Broker Conversations 630 How It Works 631 Creating a Stored Procedure to Process Messages 632 Creating the Bookstore Stored Procedure 632 How It Works 634 Remote-Server Service Broker Implementations 635 Enabling Transport Security 637 How It Works 640 Enabling Dialog Security 640 How It Works 642 Creating Routes and Remote Service Bindings 642 How It Works 645 Event Notifications 646 Capturing Login Commands 646 How It Works 648 Configuring and Viewing SQL Server Options 649 Unknown -1 Viewing SQL Server Configurations 649 How It Works 651 Changing SQL Server Configurations 651 How It Works 653 Creating and Configuring Databases 655 Creating, Altering, and Dropping Databases 655 Creating a Database with a Default Configuration 656 How It Works 656 Viewing Database Information 656 How It Works 657 Creating a Database Using File Options 658 How It Works 659 Creating a Database with a User-Defined Filegroup 661 How It Works 662 Setting Database User Access 662 How It Works 664 Renaming a Database 665 How It Works 665 Dropping a Database 666 How It Works 666 Detaching a Database 666 How It Works 668 Attaching a Database 668 How It Works 669 Configuring Database Options 669 Viewing Database Options 670 How It Works 670 Configuring ANSI SQL Options 670 How It Works 672 Configuring Automatic Options 672 How It Works 673 Creating or Modifying a Database to Allow External Access 674 How It Works 674 Creating or Changing a Database to Use a Non-Server Default Collation 675 How It Works 676 Configuring Cursor Options 676 How It Works 677 Enabling Date Correlation Optimization 677 How It Works 678 Modifying Database Parameterization Behavior 678 How It Works 680 Enabling Read Consistency for a Transaction 681 How It Works 682 Configuring Database Recovery Models 683 How It Works 684 Configuring Page Verification 684 How It Works 685 Controlling Database Access and Ownership 685 Changing a Database State to Online, Offline, or Emergency 686 How It Works 686 Changing a Database Owner 687 How It Works 687 Managing Database Files and Filegroups 688 Adding a Data File or Log File to an Existing Database 688 How It Works 689 Removing a Data or Log File from a Database 690 How It Works 690 Relocating a Data or Transaction Log File 691 How It Works 692 Changing a File’s Logical Name 692 How It Works 692 Increasing a Database’s File Size and Modifying Its Growth Options 693 How It Works 693 Adding a Filegroup to an Existing Database 694 How It Works 694 Setting the Default Filegroup 694 How It Works 695 Removing a Filegroup 695 How It Works 696 Making a Database or Filegroup Read-Only 696 How It Works 697 Viewing and Managing Database Space Usage 697 Viewing Database Space Usage 697 How It Works 699 Shrinking the Database or a Database File 699 How It Works 702 Database Integrity and Optimization 703 Database Integrity Checking 703 Checking Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC 704 How It Works 705 Checking Allocation and Structural Integrity with DBCC CHECKDB 706 How It Works 708 Tables and Constraints 708 Checking Allocation and Structural Integrity of All Tables in a Filegroup Using DBCC CHECKFILEGROUP 709 How It Works 710 Checking Data Integrity for Tables and Indexed Views Using DBCC CHECKTABLE 710 How It Works 713 Checking Table Integrity with DBCC CHECKCONSTRAINTS 713 How It Works 714 Checking System Table Consistency with DBCC CHECKCATALOG 715 How It Works 716 Index Maintenance 716 Rebuilding Indexes 716 How It Works 718 Defragmenting Indexes 719 How It Works 719 Rebuilding a Heap 720 How It Works 720 Maintaining Database Objects and Object Dependencies 721 Database Object Maintenance 721 Changing the Name of a User-Created Database Object 721 How It Works 722 Changing an Object’s Schema 723 How It Works 723 Object Dependencies 724 Identifying Object Dependencies 724 How It Works 726 Identifying Referencing and Referenced Entities 726 How It Works 727 Viewing an Object’s Definition 728 How It Works 729 Database Mirroring 731 Database Mirroring in Context 731 Database Mirroring Architecture 732 Setting Up Database Mirroring 734 Creating Mirroring Endpoints 734 How It Works 738 Backing Up and Restoring Principal Databases 739 How It Works 740 Creating a Database Mirroring Session 741 How It Works 743 Setup Summary 743 Operating Database Mirroring 744 Changing Operating Modes 745 How It Works 745 Performing Failovers 746 How It Works 747 Pausing or Resuming a Mirroring Session 747 How It Works 747 Stopping Mirroring Sessions and Removing Endpoints 748 How It Works 748 Monitoring and Configuring Options 748 Mon

SQL Server 2008 Transact–SQL Recipes: A Problem–Solution Approach is an example–based guide to the Transact–SQL language that is at the core of SQL Server 2008. Learn to create databases, insert and update data, generate reports, secure your data, and more. Author Joseph Sack takes common Transact–SQL tasks and breaks them down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on.

• Focused on solutions: Look up what you need to do. Learn how to do it. Do it.
• Current: Newly updated for SQL Server 2008
• Comprehensive: Covers 30 different Transact–SQL problem domains

What you’ll learn

• Create databases, tables, and indexes
• Query and manipulate data
• Store and manage XML inside the database
• Move business logic into the database
• Encrypt data and capture changes for compliance purposes
• Implement full–text search
• Interface with Service Broker

Who this book is for

Developers who use Microsoft SQL Server 2008 as their back–end database. Database administrators who create, manage, and secure those databases.

SQL Server 2008 Transact–SQL Recipes: A Problem–Solution Approach is an example–based guide to the Transact–SQL language that is at the core of SQL Server 2008. Learn to create databases, insert and update data, generate reports, secure your data, and more. Author Joseph Sack takes common Transact–SQL tasks and breaks them down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Focused on solutions: Look up what you need to do. Learn how to do it. Do it. Current: Newly updated for SQL Server 2008. Comprehensive: Covers 30 different Transact–SQL problem domains. What you’ll learn Create databases, tables, and indexes. Query and manipulate data. Store and manage XML inside the database. Move business logic into the database. Encrypt data and capture changes for compliance purposes. Implement Full–Text Search. Interface with Service Broker. And more! Who is this book for? Developers who use Microsoft SQL Server 2008 as their back–end database. Database administrators who create, manage, and secure those databases. __SQL Server 2008 Transact–SQL Recipes: A Problem–Solution Approach__ is an example–based guide to the Transact–SQL language that is at the core of SQL Server 2008. Learn to create databases, insert and update data, generate reports, secure your data, and more. Author **Joseph Sack** takes common Transact–SQL tasks and breaks them down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. * Focused on solutions: Look up what you need to do. Learn how to do it. Do it. * Current: Newly updated for SQL Server 2008. * Comprehensive: Covers 30 different Transact–SQL problem domains. ## What you’ll learn * Create databases, tables, and indexes. * Query and manipulate data. * Store and manage XML inside the database. * Move business logic into the database. * Encrypt data and capture changes for compliance purposes. * Implement Full–Text Search. * Interface with Service Broker. * And more! ## Who is this book for? Developers who use Microsoft SQL Server 2008 as their back–end database. Database administrators who create, manage, and secure those databases.