Contents at a Glance
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
¦CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
¦CHAPTER 2 INSERT, UPDATE, DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
¦CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking . . . . . . . . . . . . . . 83
¦CHAPTER 4 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
¦CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
¦CHAPTER 6 Full-Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
¦CHAPTER 7 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
¦CHAPTER 8 SQL Server Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
¦CHAPTER 9 Conditional Processing, Control-Of-Flow, and Cursors. . . . . . . . . . 255
¦CHAPTER 10 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
¦CHAPTER 11 User-Defined Functions and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
¦CHAPTER 12 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
¦CHAPTER 13 CLR Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
¦CHAPTER 14 XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
¦CHAPTER 15 Web Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
¦CHAPTER 16 Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
¦CHAPTER 17 Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
¦CHAPTER 18 Securables and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
¦CHAPTER 19 Encryption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
¦CHAPTER 20 Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
¦CHAPTER 21 Configuring and Viewing SQL Server Options . . . . . . . . . . . . . . . . . . 517
¦CHAPTER 22 Creating and Configuring Databases . . . . . . . . . . . . . . . . . . . . . . . . . . 523
iii
¦CHAPTER 23 Database Integrity and Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . 571
¦CHAPTER 24 Maintaining Database Objects and Object Dependencies. . . . . . . 589
¦CHAPTER 25 Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
¦CHAPTER 26 Database Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
¦CHAPTER 27 Linked Servers and Distributed Queries . . . . . . . . . . . . . . . . . . . . . . . 621
¦CHAPTER 28 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
¦CHAPTER 29 Backup and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717
iv
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
¦CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
The Basic SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Selecting Specific Columns for Every Row . . . . . . . . . . . . . . . . . . . . . . 2
Selecting Every Column for Every Row . . . . . . . . . . . . . . . . . . . . . . . . . 3
Selective Querying Using a Basic WHERE Clause . . . . . . . . . . . . . . . . . . . . . 3
Using the WHERE Clause to Specify Rows Returned
in the Result Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Combining Search Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Negating a Search Condition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Keeping Your WHERE Clause Unambiguous . . . . . . . . . . . . . . . . . . . . . 6
Using Operators and Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Using BETWEEN for Date Range Searches . . . . . . . . . . . . . . . . . . . . . . 8
Using Comparisons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Checking for NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Returning Rows Based on a List of Values . . . . . . . . . . . . . . . . . . . . . 10
Using Wildcards with LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Ordering Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Using the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Using the TOP Keyword with Ordered Results . . . . . . . . . . . . . . . . . . 13
Grouping Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Using GROUP BY ALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Selectively Querying Grouped Data Using HAVING. . . . . . . . . . . . . . . 17
v
SELECT Clause Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Using DISTINCT to Remove Duplicate Values . . . . . . . . . . . . . . . . . . 19
Using DISTINCT in Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . 19
Using Column Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Using SELECT to Create a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Performing String Concatenation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Creating a Comma Delimited List Using SELECT . . . . . . . . . . . . . . . . 22
Using the INTO Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
SubQueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Using Subqueries to Check for the Existence of Matches . . . . . . . . 25
Querying from More Than One Data Source. . . . . . . . . . . . . . . . . . . . . . . . . 26
Using INNER Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Using OUTER Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Using CROSS Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Performing Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Using Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Combining Result Sets with UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Using APPLY to Invoke a Table-Valued Function for Each Row . . . . . . . . . 33
Using CROSS APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Using OUTER APPLY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Data Source Advanced Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Using the TABLESAMPLE to Return Random Rows . . . . . . . . . . . . . . 37
Using PIVOT to Convert Single Column Values into Multiple
Columns and Aggregate Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Normalizing Data with UNPIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Returning Distinct or Matching Rows Using EXCEPT and
INTERSECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Summarizing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Summarizing Data with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Using GROUPING with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Summarizing Data with WITH ROLLUP. . . . . . . . . . . . . . . . . . . . . . . . . 46
Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Using Join Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Using Query Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Using Table Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Common Table Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Using a Non-Recursive Common Table Expression (CTE) . . . . . . . . . 53
Using a Recursive Common Table Expression (CTE) . . . . . . . . . . . . . 56
vi ¦CONTENTS
¦CHAPTER 2 INSERT, UPDATE, DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
INSERT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Inserting a Row into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Inserting a Row Using Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . 63
Explicitly Inserting a Value into an IDENTITY Column . . . . . . . . . . . . 64
Inserting a Row into a Table with a Uniqueidentifier Column. . . . . . 65
Inserting Rows Using an INSERT...SELECT Statement. . . . . . . . . . . . 66
Inserting Data from a Stored Procedure Call. . . . . . . . . . . . . . . . . . . . 67
UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Updating a Single Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Updating Rows Based on a FROM and WHERE Clause . . . . . . . . . . . 70
Updating Large Value Data Type Columns. . . . . . . . . . . . . . . . . . . . . . 71
Inserting or Updating an Image File Using OPENROWSET
and BULK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Truncating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
The OUTPUT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Using the OUTPUT Clause with INSERT, UPDATE, DELETE . . . . . . . . 78
Chunking Data Modifications with TOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Deleting Rows in Chunks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
¦CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking. . . . . . . 83
Transaction Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Using Explicit Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Displaying the Oldest Active Transaction with
DBCC OPENTRAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Viewing Lock Activity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Transaction, Locking, and Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Using SET TRANSACTION ISOLATION LEVEL . . . . . . . . . . . . . . . . . . . . 94
Blocking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Identifying and Resolving Blocking Processes . . . . . . . . . . . . . . . . . . 99
Using SET LOCK TIMEOUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Deadlocking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Identifying Deadlocks with a Trace Flag. . . . . . . . . . . . . . . . . . . . . . . 103
Setting Deadlock Priority . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
¦CONTENTS vii
¦CHAPTER 4 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Table Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Adding a Column to an Existing Table . . . . . . . . . . . . . . . . . . . . . . . . 112
Changing a Column Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Creating a Computed Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Dropping a Table Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Reporting Table Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Collation Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Viewing Collation Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Designating a Column’s Collation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Creating a Table with a Primary Key. . . . . . . . . . . . . . . . . . . . . . . . . . 120
Adding a Primary Key Constraint to an Existing Table . . . . . . . . . . . 121
Creating a Table with a Foreign Key Reference . . . . . . . . . . . . . . . . 121
Adding a Foreign Key to an Existing Table. . . . . . . . . . . . . . . . . . . . . 122
Creating Recursive Foreign Key References . . . . . . . . . . . . . . . . . . . 123
Allowing Cascading Changes in Foreign Keys . . . . . . . . . . . . . . . . . 124
Surrogate Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Using the IDENTITY Property During Table Creation . . . . . . . . . . . . 126
Using DBCC CHECKIDENT to View and Correct IDENTITY
Seed Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Using the ROWGUIDCOL Property . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Creating a Unique Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Adding a UNIQUE Constraint to an Existing Table. . . . . . . . . . . . . . . 131
Using CHECK Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Adding a CHECK Constraint to an Existing Table . . . . . . . . . . . . . . . 133
Disabling and Enabling a Constraint. . . . . . . . . . . . . . . . . . . . . . . . . . 134
Using a DEFAULT Constraint During Table Creation . . . . . . . . . . . . . 135
Adding a DEFAULT Constraint to an Existing Table. . . . . . . . . . . . . . 136
Dropping a Constraint from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Temporary Tables and Table Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Using a Temporary Table for Multiple Lookups Within
a Batch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Creating a Table Variable to Hold a Temporary Result Set . . . . . . . 140
viii ¦CONTENTS
Manageability for Very Large Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Implementing Table Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Determining the Location of Data in a Partition . . . . . . . . . . . . . . . . 145
Adding a New Partition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Removing a Partition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Moving a Partition to a Different Table . . . . . . . . . . . . . . . . . . . . . . . . 149
Removing Partition Functions and Schemes. . . . . . . . . . . . . . . . . . . 151
Placing a Table on a Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
¦CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Indexes Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Create a Table Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Enforce Uniqueness on Non-Key Columns . . . . . . . . . . . . . . . . . . . . 159
Create a Composite Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Define Index Column Sort Direction . . . . . . . . . . . . . . . . . . . . . . . . . . 161
View Index Meta Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Disable an Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Dropping Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Change an Existing Index with DROP_EXISTING . . . . . . . . . . . . . . . 164
Controlling Index Build Performance and Concurrency. . . . . . . . . . . . . . . 164
Intermediate Index Creation in Tempdb . . . . . . . . . . . . . . . . . . . . . . . 165
Controlling Parallel Plan Execution for Index Creation. . . . . . . . . . . 165
Allowing User Table Access During Index Creation . . . . . . . . . . . . . 166
Index Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Using an Index INCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Using PAD_INDEX and FILLFACTOR . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Disabling Page and/or Row Index Locking . . . . . . . . . . . . . . . . . . . . 168
Managing Very Large Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Creating an Index on a Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Implementing Index Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
¦CHAPTER 6 Full-Text Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Full-Text Indexes and Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Creating a Full-Text Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Creating a Full-Text Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Modifying a Full-Text Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Modifying a Full-Text Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Dropping a Full-Text Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Dropping a Full-Text Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
ta. . . . . . . . . . . . . . . 181
¦CONTENTS ix
Basic Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Using FREETEXT to Search Full-Text Indexed Columns . . . . . . . . . 183
Using CONTAINS for Word Searching . . . . . . . . . . . . . . . . . . . . . . . . . 184
Advanced Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Using CONTAINS to Search with Wildcards . . . . . . . . . . . . . . . . . . . . 185
Using CONTAINS to Search for Inflectional Matches . . . . . . . . . . . . 185
Using CONTAINS for Searching Results by Term Proximity . . . . . . 186
Ranked Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Returning Ranked Search Results by Meaning. . . . . . . . . . . . . . . . . 187
Returning Ranked Search Results by Weighted Value. . . . . . . . . . . 188
¦CHAPTER 7 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Regular Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Creating a Basic View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Querying the View Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Reporting on Database Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Refreshing a View’s Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Modifying a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Dropping a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Modifying Data Through a View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
View Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Encrypting a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Creating an Indexed View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Forcing the Optimizer to Use an Index for an Indexed View . . . . . 202
Partitioned Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Creating a Distributed-Partitioned View. . . . . . . . . . . . . . . . . . . . . . . 203
¦CHAPTER 8 SQL Server Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Returning the Average of Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Returning Row Counts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Finding the Lowest and Highest Values from an Expression . . . . . 211
Returning the Sum of Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Using Statistical Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . 212
Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Using Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
x ¦CONTENTS
String Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Converting a Character Value to ASCII and Back to Character. . . . 216
Returning Integer and Character Unicode Values. . . . . . . . . . . . . . . 216
Finding the Start Position of a String Within Another String . . . . . . 217
Finding the Start Position of a String Within Another String
Using Wildcards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Determining the Similarity of Strings . . . . . . . . . . . . . . . . . . . . . . . . . 218
Taking the Leftmost or Rightmost Part of a String . . . . . . . . . . . . . . 219
Determining the Number of Characters or Bytes in a String . . . . . 220
Replacing a Part of a String with Another String . . . . . . . . . . . . . . . 220
Stuffing a String into a String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Changing Character Values to Lower, Upper, and Proper
Case . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Removing Leading and Trailing Blanks . . . . . . . . . . . . . . . . . . . . . . . 224
Repeating an Expression N Number of Times. . . . . . . . . . . . . . . . . . 224
Repeating a Blank Space N Number of Times . . . . . . . . . . . . . . . . . 224
Outputting an Expression in Reverse Order. . . . . . . . . . . . . . . . . . . . 225
Returning a Chunk of an Expression . . . . . . . . . . . . . . . . . . . . . . . . . 225
Working with NULLs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Replacing a NULL Value with an Alternative Value . . . . . . . . . . . . . . 226
Performing Flexible Searches Using ISNULL. . . . . . . . . . . . . . . . . . . 226
Returning the First Non NULL Value in a List of Expressions . . . . . 228
Returning a NULL Value When Two Expressions Are Equal:
Otherwise Return the First Expression . . . . . . . . . . . . . . . . . . . . . 228
Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Returning the Current Date and Time. . . . . . . . . . . . . . . . . . . . . . . . . 230
Incrementing or Decrementing a Date’s Value . . . . . . . . . . . . . . . . . 230
Finding the Difference Between Two Dates. . . . . . . . . . . . . . . . . . . . 231
Displaying the String Value for Part of a Date . . . . . . . . . . . . . . . . . . 232
Displaying the Integer Value for Part of a Date Using
DATEPART . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Displaying the Integer Value for Part of a Date Using
YEAR, MONTH, and DAY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Converting Data Types Using Convert and Cast . . . . . . . . . . . . . . . . . . . . . 234
Converting Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Performing Date Conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Evaluating Whether an Expression Is a Date or Is Numeric . . . . . . 236
¦CONTENTS xi
Ranking Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Using an Incrementing Row Number . . . . . . . . . . . . . . . . . . . . . . . . . 237
Returning Rows by Rank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Returning Rows by Rank Without Gaps . . . . . . . . . . . . . . . . . . . . . . . 240
Using NTILE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Probing Server, Database, and Connection-Level Settings
Using System Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Using SQL Server’s First Day of the Week Setting . . . . . . . . . . . . . . 242
Viewing the Language Used in the Current Session . . . . . . . . . . . . 242
Viewing and Setting Current Connection Lock Timeout
Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Displaying the Nesting Level for the Current Stored
Procedure Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Returning the Current SQL Server Instance Name and
SQL Server Version. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Returning the Current Connection’s Session ID (SPID) . . . . . . . . . . 244
Returning Number of Open Transactions . . . . . . . . . . . . . . . . . . . . . 244
Retrieving the Rows Affected by the Previous Statement. . . . . . . . 245
Using System Statistical Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Displaying Database and SQL Server Settings . . . . . . . . . . . . . . . . . 248
Returning the Current Database ID and Name . . . . . . . . . . . . . . . . . 248
Returning a Database Object Name and ID. . . . . . . . . . . . . . . . . . . . 249
Returning the Application and Host for the Current User
Session. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Reporting Current User and Login Context . . . . . . . . . . . . . . . . . . . . 250
Viewing User Connection Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
IDENTITY and uniqueidentifier Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Returning the Last Identity Value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Returning an Identity Column’s Seed and Incrementing
Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Creating a New uniqueidentifier Value . . . . . . . . . . . . . . . . . . . . . . . . 253
¦CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors . . 255
Conditional Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Using CASE to Evaluate a Single Input Expression. . . . . . . . . . . . . . 256
Using CASE to Evaluate Boolean Expressions. . . . . . . . . . . . . . . . . . 257
Using IF...ELSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
xii ¦CONTENTS
Control-of-Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Using RETURN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Using WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Using GOTO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
Using WAITFOR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Creating and Using Transact-SQL Cursors . . . . . . . . . . . . . . . . . . . . 268
¦CHAPTER 10 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Stored Procedure Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Creating a Basic Stored Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Creating a Parameterized Stored Procedure. . . . . . . . . . . . . . . . . . . 274
Using OUTPUT Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
Modifying a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Dropping Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Executing Stored Procedures Automatically at SQL Server
Startup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Reporting Stored Procedure Metadata. . . . . . . . . . . . . . . . . . . . . . . . 280
Documenting Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Stored Procedure Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Encrypting a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Using EXECUTE AS to Specify the Procedure’s Security
Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Recompilation and Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
RECOMPILE(ing) a Stored Procedure Each Time It Is
Executed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Flushing the Procedure Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
¦CHAPTER 11 User-Defined Functions and Types . . . . . . . . . . . . . . . . . . . . . . . 289
UDF Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Creating Scalar User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . 290
Creating Inline User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . 293
Creating Multi-Statement User-Defined Functions . . . . . . . . . . . . . 295
Modifying User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Viewing UDF Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Dropping User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Benefiting From UDFs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Using Scalar UDFs to Maintain Reusable Code. . . . . . . . . . . . . . . . . 301
Using Scalar UDFs to Cross Reference Natural Key Values . . . . . . 303
tement UDFs . . . . . . . . . . . . . . . . . . 306
¦CONTENTS xiii
UDT Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Creating and Using User-Defined Types. . . . . . . . . . . . . . . . . . . . . . . 308
Identifying Columns and Parameters That Use
User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Dropping User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
¦CHAPTER 12 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Creating an AFTER DML Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Creating an INSTEAD OF DML Trigger . . . . . . . . . . . . . . . . . . . . . . . . 318
Using DML Triggers and Transactions . . . . . . . . . . . . . . . . . . . . . . . . 321
Controlling DML Triggers Based on Modified Columns . . . . . . . . . . 323
Viewing DML Trigger Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
DDL Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Creating a DDL Trigger That Audits Database-Level Events. . . . . . 326
Creating a DDL Trigger That Audits Server-Level Events . . . . . . . . 328
Viewing DDL Trigger Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Managing Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
Modifying a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
Enabling and Disabling Table Triggers . . . . . . . . . . . . . . . . . . . . . . . . 330
Limiting Trigger Nesting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Controlling Trigger Recursion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Setting Trigger Firing Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Dropping a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
¦CHAPTER 13 CLR Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
CLR Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
When (and When Not) to Use Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . 338
CLR Objects Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Creating CLR Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Enabling CLR Support in SQL Server 2005 . . . . . . . . . . . . . . . . . . . . 341
Writing an Assembly for a CLR Stored Procedure . . . . . . . . . . . . . . 341
Compiling an Assembly into a DLL File . . . . . . . . . . . . . . . . . . . . . . . 344
Loading the Assembly Into SQL Server . . . . . . . . . . . . . . . . . . . . . . . 345
Creating the CLR Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Creating a CLR Scalar User-Defined Function . . . . . . . . . . . . . . . . . 347
Creating a CLR Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
xiv ¦CONTENTS
Administering Assemblies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Viewing Assembly Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Modifying an Assembly’s Permissions . . . . . . . . . . . . . . . . . . . . . . . . 352
Removing an Assembly from the Database . . . . . . . . . . . . . . . . . . . 353
¦CHAPTER 14 XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
XML and Related Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Working with Native XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Creating XML Data Type Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Inserting XML Data into a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Validating XML Data Using Schemas . . . . . . . . . . . . . . . . . . . . . . . . . 360
Retrieving XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Modifying XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Using XML Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Converting Between XML Documents and Relational Data . . . . . . . . . . 367
Using FOR XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Using OPENXML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
¦CHAPTER 15 Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Web Service Technologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
HTTP Endpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
Creating an HTTP Endpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Managing HTTP Endpoint Security . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Modifying an HTTP Endpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Removing an HTTP Endpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Reserving Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Creating a .NET Client That Uses a Web Service . . . . . . . . . . . . . . . . . . . . 387
¦CHAPTER 16 Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
System-Defined and User-Defined Error Messages . . . . . . . . . . . . . . . . . 393
Viewing System Error Information . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Creating a User-Defined Error Message . . . . . . . . . . . . . . . . . . . . . . 394
Dropping a User-Defined Error Message . . . . . . . . . . . . . . . . . . . . . . 396
Using RAISERROR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Invoking an Error Message Using RAISERROR . . . . . . . . . . . . . . . . . 397
Using TRY...CATCH. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Old Style Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Error Handling with TRY...CATCH. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
¦CONTENTS xv
Applying TRY...CATCH Error Handling Without Recoding
a Stored Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Nesting TRY...CATCH Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
¦CHAPTER 17 Principals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Windows Principals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Creating a Windows Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
Viewing Windows Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Altering a Windows Login. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Dropping a Windows Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
SQL Server Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Creating a SQL Server Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414
Viewing SQL Server Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Altering a SQL Server Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Dropping a SQL Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Managing Server Role Members. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Reporting Fixed Server Role Information . . . . . . . . . . . . . . . . . . . . . . 418
Database Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Creating Database Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Reporting Database User Information . . . . . . . . . . . . . . . . . . . . . . . . 422
Modifying a Database User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Removing a Database User from the Database . . . . . . . . . . . . . . . . 423
Fixing Orphaned Database Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Reporting Fixed Database Roles Information . . . . . . . . . . . . . . . . . . 425
Managing Fixed Database Role Membership . . . . . . . . . . . . . . . . . . 427
Managing User-Defined Database Roles . . . . . . . . . . . . . . . . . . . . . . 427
Managing Application Roles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
¦CHAPTER 18 Securables and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
Permissions Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
Reporting SQL Server 2005 Assignable Permissions . . . . . . . . . . . 435
Server-Scoped Securables and Permissions . . . . . . . . . . . . . . . . . . . . . . . 437
Managing Server Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Database-Scoped Securables and Permissions. . . . . . . . . . . . . . . . . . . . . 440
Managing Database Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Schema-Scoped Securables and Permissions. . . . . . . . . . . . . . . . . . . . . . 442
Managing Schemas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Managing Schema Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
xvi ¦CONTENTS
Object Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Managing Object Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Managing Permissions Across Securable Scopes . . . . . . . . . . . . . . . . . . . 451
Determining a Current Connection’s Permissions to
a Securable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
Reporting the Permissions For a Principal by Securable
Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Changing Securable Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
Allowing SQL Logins to Access Non-SQL Server Resources . . . . . 457
¦CHAPTER 19 Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Encryption by Passphrase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Using a Function to Encrypt by Passphrase . . . . . . . . . . . . . . . . . . . 459
Master Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Backing Up and Restoring a Service Master Key . . . . . . . . . . . . . . . 462
Creating, Regenerating, and Dropping a Database Master Key. . . . . 463
Backing Up and Restoring a Database Master Key . . . . . . . . . . . . . 464
Removing Service Master Key Encryption from the Database
Master Key. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
Asymmetric Key Encryption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466
Creating an Asymmetric Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466
Viewing Asymmetric Keys in the Current Database. . . . . . . . . . . . . 467
Modifying the Asymmetric Key’s Private Key Password . . . . . . . . . 468
Encrypting and Decrypting Data Using an Asymmetric Key . . . . . . 468
Dropping an Asymmetric Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Symmetric Key Encryption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Creating a Symmetric Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Viewing Symmetric Keys in the Current Database. . . . . . . . . . . . . . 473
Changing How a Symmetric Key Is Encrypted . . . . . . . . . . . . . . . . . 473
Using Symmetric Key Encryption and Decryption . . . . . . . . . . . . . . 474
Dropping a Symmetric Key. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Certificate Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Creating a Database Certificate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Viewing Certificates in the Database . . . . . . . . . . . . . . . . . . . . . . . . . 479
Backing Up and Restoring a Certificate . . . . . . . . . . . . . . . . . . . . . . . 480
Managing a Certificate’s Private Key . . . . . . . . . . . . . . . . . . . . . . . . . 481
Using Certificate Encryption and Decryption. . . . . . . . . . . . . . . . . . . 482
¦CONTENTS xvii
¦CHAPTER 20 Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Example Scenario: Online Bookstore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
Creating a Basic Service Broker Application. . . . . . . . . . . . . . . . . . . . . . . . 486
Enabling Databases for Service Broker Activity . . . . . . . . . . . . . . . . 487
Creating the Database Master Key for Encryption . . . . . . . . . . . . . . 487
Managing Message Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488
Creating Contracts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490
Creating Queues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Creating Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Initiating a Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Querying the Queue for Incoming Messages . . . . . . . . . . . . . . . . . . 496
Receiving and Responding to a Message . . . . . . . . . . . . . . . . . . . . . 497
Ending a Conversation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499
Creating a Stored Procedure to Process Messages . . . . . . . . . . . . . . . . . 501
Creating the Bookstore Stored Procedure . . . . . . . . . . . . . . . . . . . . . 501
Remote-Server Service Broker Implementations. . . . . . . . . . . . . . . . . . . . 504
Enabling Transport Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
Enabling Dialog Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Creating Routes and Remote Service Bindings . . . . . . . . . . . . . . . . 511
Event Notifications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514
Capturing Login Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514
¦CHAPTER 21 Configuring and Viewing SQL Server Options . . . . . . . . . . . . 517
Viewing SQL Server Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517
Changing SQL Server Configurations . . . . . . . . . . . . . . . . . . . . . . . . . 519
¦CHAPTER 22 Creating and Configuring Databases . . . . . . . . . . . . . . . . . . . . . 523
Creating, Altering, and Dropping Databases . . . . . . . . . . . . . . . . . . . . . . . . 523
Creating a Database with a Default Configuration . . . . . . . . . . . . . . 524
Viewing Database Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Creating a Database Using File Options. . . . . . . . . . . . . . . . . . . . . . . 525
Creating a Database with a User-Defined Filegroup . . . . . . . . . . . . 528
Setting Database User Access. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
Renaming a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
Dropping a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Detaching a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Attaching a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
xviii ¦CONTENTS
Configuring Database Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Viewing Database Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Configuring ANSI SQL Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
Configuring Automatic Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540
Creating or Modifying a Database to Allow External Access . . . . . 542
Creating or Changing a Database to Use a Non-Server
Default Collation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
Configuring Cursor Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
Enabling Date Correlation Optimization . . . . . . . . . . . . . . . . . . . . . . . 545
Modifying Database Parameterization Behavior. . . . . . . . . . . . . . . . 546
Enabling Read Consistency for a Transaction . . . . . . . . . . . . . . . . . . 549
Configuring Database Recovery Models . . . . . . . . . . . . . . . . . . . . . . 551
Configuring Page Verification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
Controlling Database Access and Ownership . . . . . . . . . . . . . . . . . . . . . . . 554
Changing a Database State to Online, Offline, or Emergency . . . . 554
Changing a Database Owner . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555
Managing Database Files and Filegroups . . . . . . . . . . . . . . . . . . . . . . . . . . 556
Adding a Data File or Log File to an Existing Database. . . . . . . . . . 556
Removing a Data or Log File from a Database . . . . . . . . . . . . . . . . . 558
Relocating a Data or Transaction Log File . . . . . . . . . . . . . . . . . . . . . 559
Changing a File’s Logical Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 560
Increasing a Database’s File Size and Modifying Its
Growth Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Adding a Filegroup to an Existing Database . . . . . . . . . . . . . . . . . . . 562
Setting the Default Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Removing a Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Making a Database or Filegroup Read-Only . . . . . . . . . . . . . . . . . . . 564
Viewing and Managing Database Space Usage. . . . . . . . . . . . . . . . . . . . . 565
Viewing Database Space Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
Shrinking the Database or a Database File . . . . . . . . . . . . . . . . . . . . 567
¦CHAPTER 23 Database Integrity and Optimization . . . . . . . . . . . . . . . . . . . . . 571
Database Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Checking Consistency of the Disk Space Allocation Structures
with DBCC CHECKALLOC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
Checking Allocation and Structural Integrity of All Database
Objects with DBCC CHECKDB. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
¦CONTENTS xix
Tables and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
Checking Allocation and Structural Integrity of All Tables
in a Filegroup Using DBCC CHECKFILEGROUP. . . . . . . . . . . . . . . 576
Checking Data Integrity for Tables and Indexed Views Using
DBCC CHECKTABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
Checking Table Integrity with DBCC CHECKCONSTRAINTS . . . . . . 580
Checking System Table Consistency with DBCC
CHECKCATALOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
Index Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583
Rebuilding Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583
Defragmenting Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586
¦CHAPTER 24 Maintaining Database Objects and Object
Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Database Object Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Changing the Name of a User-Created Database Object . . . . . . . . 589
Changing an Object’s Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
Object Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Displaying Information on Database Object Dependencies . . . . . . 592
Viewing an Object’s Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
¦CHAPTER 25 Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
Database Mirroring in Context . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596
Database Mirroring Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
Setting Up Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
Creating Mirroring Endpoints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
Backing Up and Restoring Principal Databases . . . . . . . . . . . . . . . . 602
Creating a Database Mirroring Session . . . . . . . . . . . . . . . . . . . . . . . 604
Setup Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Operating Database Mirroring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
Changing Operating Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
Performing Failovers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609
Pausing or Resuming a Mirroring Session. . . . . . . . . . . . . . . . . . . . . 610
Stopping Mirroring Sessions and Removing Endpoints. . . . . . . . . . 611
Monitoring and Configuring Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
Monitoring Mirror Status. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Reducing Failover Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Configuring the Connection Timeout Period . . . . . . . . . . . . . . . . . . . 613
xx ¦CONTENTS
¦CHAPTER 26 Database Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
Snapshots Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
Creating and Querying Database Snapshots . . . . . . . . . . . . . . . . . . 616
Removing a Database Snapshot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
Recovering Data with a Database Snapshot . . . . . . . . . . . . . . . . . . . 618
¦CHAPTER 27 Linked Servers and Distributed Queries. . . . . . . . . . . . . . . . . . 621
Linked Server Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622
Creating a Linked Server to Another SQL Server Instance . . . . . . . 622
Configuring Linked Server Properties. . . . . . . . . . . . . . . . . . . . . . . . . 623
Viewing Linked Server Information. . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Dropping a Linked Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Linked Server Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626
Adding a Linked Server Login Mapping . . . . . . . . . . . . . . . . . . . . . . . 626
Viewing Linked Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Dropping a Linked Server Login Mapping . . . . . . . . . . . . . . . . . . . . . 628
Executing Distributed Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628
Executing Distributed Queries Against a Linked Server . . . . . . . . . 628
Creating and Using an Alias to Reference Four-Part Linked
Server Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
Executing Distributed Queries Using OPENQUERY . . . . . . . . . . . . . . 631
Executing Ad Hoc Queries Using OPENROWSET . . . . . . . . . . . . . . . 631
Reading Data from a File Using OPENROWSET BULK Options. . . . 633
¦CHAPTER 28 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637
Query Performance Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
Capturing and Evaluating Query Performance . . . . . . . . . . . . . . . . . . . . . . 639
Capturing High Duration Queries Using SQL Server Profiler . . . . . 639
Capturing Executing Queries Using sys.dm_exec_requests . . . . . 644
Viewing a Query’s Graphical Execution Plan . . . . . . . . . . . . . . . . . . . 645
Viewing Estimated Query Execution Plans Using
Transact-SQL Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
Forcing SQL Server 2005 to Use a Query Plan . . . . . . . . . . . . . . . . . 653
Viewing Execution Runtime Information . . . . . . . . . . . . . . . . . . . . . . 655
Viewing Performance Statistics for Cached Query Plans . . . . . . . . 657
¦CONTENTS xxi
Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659
Manually Creating Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659
Updating Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660
Generating and Updating Statistics Across All Tables . . . . . . . . . . . 661
View Statistics Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662
Removing Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664
Index Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664
Displaying Index Fragmentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665
Displaying Index Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668
Using the Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . 669
Miscellaneous Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Using an Alternative to Dynamic SQL. . . . . . . . . . . . . . . . . . . . . . . . . 675
Applying Hints Without Modifying Application SQL . . . . . . . . . . . . . 677
¦CHAPTER 29 Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
Creating a Backup and Recovery Plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685
Performing a Basic Full Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
Naming and Describing Your Backups and Media . . . . . . . . . . . . . . 689
Configuring Backup Retention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 691
Striping Backup Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692
Using a Named Backup Device . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
Mirroring Backup Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 694
Performing a Transaction Log Backup . . . . . . . . . . . . . . . . . . . . . . . 696
Using COPY ONLY Backup Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 698
Performing a Differential Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 698
Backing Up Individual Files or Filegroups . . . . . . . . . . . . . . . . . . . . . 699
Performing a Partial Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701
Viewing Backup Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702
Restoring a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705
Restoring a Database from a Full Backup . . . . . . . . . . . . . . . . . . . . 705
Restoring a Database from a Transaction Log Backup . . . . . . . . . 709
Restoring a Database from a Differential Backup . . . . . . . . . . . . . . 712
Restoring a File or Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713
Performing a Piecemeal (PARTIAL) Restore. . . . . . . . . . . . . . . . . . . . 714
Restoring a Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715
Цена: 200руб.