SQL Server 2005 T-SQL Recipes

by Sack, Joseph

• ISBN13: 9781590595701
• ISBN10: 159059570X
• Format: Paperback
• Copyright: 2005-12-12
• Publisher: Apress

Summary

Need to brush up on specific SQL Server tasks, procedures, or Transact-SQL commands? Not finding what you need from SQL Server books online? Or perhaps you just want to familiarise yourself with the practical application of new T-SQL related features. SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach is an ideal book, whatever your level as a DBA or developer. This "no-fluff" desk reference offers direct access to the information you need to get the job done. It covers basic T-SQL data manipulation, the use of stored procedures, triggers and UDFs, and advanced T-SQL techniques for database security and maintenance. It also provides hundreds of practical recipes that describe the utilities of features and functions, with a minimum of background theory.

Table of Contents

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