Joe Sack’s SQL Server blog…

Per my promise in an earlier post, here is a more detailed content list for SQL Server 2008 Transact-SQL Recipes.  ** Just a warning that this is a long post. **  Feel free to post comments if you have questions about the table of contents or topics listed.

CHAPTER 1 SELECT
The Basic SELECT Statement                                           
Selecting Specific Columns from a Table                            
Selecting Every Column for Every Row                              
Selective Querying Using a Basic WHERE Clause                          
Using the WHERE Clause to Specify Rows Returned in the Result Set    
Combining Search Conditions                                     
Negating a Search Condition                                      
Keeping Your WHERE Clause Unambiguous                          
Using Operators and Expressions                                       
Using BETWEEN for Date Range Searches                           
Using Comparisons                                              
Checking for NULL Values                                       
Returning Rows Based on a List of Values                          
Using Wildcards with LIKE                                       
Declaring and Assigning Values to Variables                        
Grouping Data                                                      
Using the GROUP BY Clause                                      
Using GROUP BY ALL                                          
Selectively Querying Grouped Data Using HAVING                   
Ordering Results                                                    
Using the ORDER BY Clause                                      
Using the TOP Keyword with Ordered Results                       
SELECT Clause Techniques                                           
Using DISTINCT to Remove Duplicate Values                        
Using DISTINCT in Aggregate Functions                            
Using Column Aliases                                           
Using SELECT to Create a Script                                  
Performing String Concatenation                                  
Creating a Comma-Delimited List Using SELECT                    
Using the INTO Clause 
Subqueries                                                         
Using Subqueries to Check for Matches                            
Querying from More Than One Data Source                              
Using INNER Joins                                              
Using OUTER Joins                                             
Using CROSS Joins                                             
Referencing a Single Table Multiple Times in the Same Query         
Using Derived Tables                                           
Combining Result Sets with UNION                                
Using APPLY to Invoke a Table-Valued Function for Each Row              
Using CROSS APPLY                                            
Using OUTER APPLY                                            
Advanced Techniques for Data Sources                                 
Using the TABLESAMPLE to Return Random Rows                   
Using PIVOT to Convert Single Column Values into Multiple Columns
and Aggregate Data                                          
Normalizing Data with UNPIVOT                                  
Returning Distinct or Matching Rows Using EXCEPT and INTERSECT    
Summarizing Data                                                   
Summarizing Data Using CUBE                                   
Summarizing Data Using ROLLUP                                 
Creating Custom Summaries Using Grouping Sets                   
Revealing Rows Generated by GROUPING                          
Advanced Group-Level Identification with GROUPING_ID              
Common Table Expressions                                           
Using a Non-Recursive Common Table Expression                   
Using a Recursive Common Table Expression                       

CHAPTER 2 Perform, Capture, and Track Data Modifications           
INSERT                                                            
Inserting a Row into a Table                                      
Inserting a Row Using Default Values                              
Explicitly Inserting a Value into an IDENTITY Column                 
Inserting a Row into a Table with a uniqueidentifier Column           
Inserting Rows Using an INSERTSELECT Statement                
Inserting Data from a Stored Procedure Call                        
Inserting Multiple Rows with VALUES                              
Using VALUES As a Table Source                                 
UPDATE                                                            
Updating a Single Row                                          
Updating Rows Based on a FROM and WHERE Clause                
Updating Large Value Data Type Columns                          
Inserting or Updating an Image File Using OPENROWSET and BULK     
Storing Unstructured Data on the File System While Maintaining
SQL Server Transactional Control                              
Assigning and Modifying Database Values “in Place”                 
DELETE                                                            
Deleting Rows                                                 
Truncating a Table                                             
Advanced Data Modification Techniques                                
Chunking Data Modifications with TOP                             
Executing INSERTs, UPDATEs, and DELETEs in a Single Statement     
Capturing and Tracking Data Modification Changes                       
Returning Rows Affected by a Data Modification Statement           
Asynchronously Capturing Table Data Modifications                 
Querying All Changes from CDC Tables                           
Querying Net Changes from CDC Tables                          
Translating the CDC Update Mask                                
Working with LSN Boundaries                                   
Disabling Change Data Capture from Tables and the Database       
Tracking Net Data Changes with Minimal Disk Overhead            

CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking        
Transaction Control                                                 
Using Explicit Transactions                                      
Displaying the Oldest Active Transaction with DBCC OPENTRAN      
Querying Transaction Information by Session                      
Locking                                                           
Viewing Lock Activity                                          
Controlling a Table’s Lock Escalation Behavior                     
Transaction, Locking, and Concurrency                                
Configuring a Session’s Transaction Locking Behavior               
Blocking                                                          
Identifying and Resolving Blocking Issues                         
Configuring How Long a Statement Will Wait for a Lock
to Be Released                                             
Deadlocking                                                       
Identifying Deadlocks with a Trace Flag                           
Setting Deadlock Priority                                       

CHAPTER 4 Tables                                                          
Table Basics                                                       
Creating a Table                                              
Adding a Column to an Existing Table                             
Changing an Existing Column Definition                           
Creating a Computed Column                                   
Reducing Storage for Null Columns                               
Dropping a Table Column                                       
Reporting Table Information                                     
Dropping a Table                                              
Collation Basics                                                    
Viewing Collation Metadata                                     
Designating a Column’s Collation                                
Keys                                                             
Creating a Table with a Primary Key                              
Adding a Primary Key Constraint to an Existing Table                
Creating a Table with a Foreign Key Reference                     
Adding a Foreign Key to an Existing Table                         
Creating Recursive Foreign Key References                        
Allowing Cascading Changes in Foreign Keys                      
Surrogate Keys                                                     
Using the IDENTITY Property During Table Creation                 
Using DBCC CHECKIDENT to View and Correct IDENTITY
Seed Values                                               
Using the ROWGUIDCOL Property                                
Constraints                                                        
Creating a Unique Constraint                                    
Adding a UNIQUE Constraint to an Existing Table                   
Using CHECK Constraints                                       
Adding a CHECK Constraint to an Existing Table                    
Disabling and Enabling a Constraint                              
Using a DEFAULT Constraint During Table Creation                 
Adding a DEFAULT Constraint to an Existing Table                  
Dropping a Constraint from a Table                              
Temporary Tables and Table Variables                                 
Using a Temporary Table for Multiple Lookups Within a Batch        
Creating a Table Variable to Hold a Temporary Result Set            
Manageability for Very Large Tables                                   
Implementing Table Partitioning                                 
Determining the Location of Data in a Partition                     
Adding a New Partition                                         
Removing a Partition                                           
Moving a Partition to a Different Table                            
Removing Partition Functions and Schemes                       
Easing VLDB Manageability with Filegroups                        
Reducing Disk Space Usage with Data Compression                

CHAPTER 5 Indexes                                                        
Index Overview                                                    
Creating a Table Index                                         
Enforcing Uniqueness on Non-Key Columns                       
Creating an Index on Multiple Columns                           
Defining Index Column Sort Direction                             
Viewing Index Meta Data                                       
Disabling an Index                                             
Dropping Indexes                                              
Changing an Existing Index with DROP_EXISTING                   
Controlling Index Build Performance and Concurrency                    
Intermediate Index Creation in Tempdb                           
Controlling Parallel Plan Execution for Index Creation                
Allowing User Table Access During Index Creation                  
Index Options                                                      
Using an Index INCLUDE                                        
Using PAD_INDEX and FILLFACTOR                              
Disabling Page and/or Row Index Locking                         
Managing Very Large Indexes                                        
Creating an Index on a Filegroup                                 
Implementing Index Partitioning                                 
Indexing a Subset of Rows                                      
Reducing Index Size                                           

CHAPTER 6 Full-Text Search                                              
Full-Text Indexes and Catalogs                                       
Creating a Full-Text Catalog                                     
Creating a Full-Text Index                                      
Modifying a Full-Text Catalog                                   
Modifying a Full-Text Index                                     
Retrieving Full-Text Catalog and Index Metadata                   
Discarding Common Strings from a Full-Text Index                 
Dropping a Full-Text Index                                      
Dropping a Full-Text Catalog                                    
Basic Searching                                                    
Using FREETEXT to Search Full-Text Indexed Columns              
Using CONTAINS for Word Searching                             
Advanced Searching                                                
Using CONTAINS to Search with Wildcards                        
Using CONTAINS to Search for Inflectional Matches                 
Using CONTAINS for Searching Results by Term Proximity           
Ranked Searching                                                  
Returning Ranked Search Results by Meaning                     
Returning Ranked Search Results by Weighted Value               

CHAPTER 7 Views                                                           
Regular Views                                                     
Creating a Basic View                                          
Querying the View Definition                                    
Displaying Views and Their Structures                            
Refreshing a View’s Definition                                   
Modifying a View                                              
Dropping a View                                              
Modifying Data Through a View                                  
View Encryption                                                    
Encrypting a View                                             
Indexed Views                                                     
Creating an Indexed View                                       
Forcing the Optimizer to Use an Index for an Indexed View           
Partitioned Views                                                   
Creating a Distributed-Partitioned View                           

CHAPTER 8 SQL Server Functions                                         
Aggregate Functions                                                
Returning the Average of Values                                 
Returning Row Counts                                         
Finding the Lowest and Highest Values from an Expression          
Returning the Sum of Values                                    
Using Statistical Aggregate Functions                             
Mathematical Functions                                             
Performing Mathematical Operations                             
String Functions                                                    
Converting a Character Value to ASCII and Back to Character         
Returning Integer and Character Unicode Values                   
Finding the Start Position of a String Within Another String           
Finding the Start Position of a String Within Another String Using Wildcards                                            
Determining the Similarity of Strings                             
Taking the Leftmost or Rightmost Part of a String                  
Determining the Number of Characters or Bytes in a String          
Replacing a Part of a String                                     
Stuffing a String into a String                                    
Changing Between Lower- and Uppercase                        
Removing Leading and Trailing Blanks                            
Repeating an Expression N Number of Times                      
Repeating a Blank Space N Number of Times                      
Outputting an Expression in Reverse Order                        
Returning a Chunk of an Expression                              
Working with NULLs                                                
Replacing a NULL Value with an Alternative Value                  
Performing Flexible Searches Using ISNULL                       
Returning the First Non-NULL Value in a List of Expressions          
Returning a NULL Value When Two Expressions Are Equal: Otherwise Returning the First Expression                                
Date Functions                                                     
Returning the Current Date and Time                             
Converting Between Time Zones                                 
Incrementing or Decrementing a Date’s Value                      
Finding the Difference Between Two Dates                        
Displaying the String Value for Part of a Date                      
Displaying the Integer Representation for Parts of a Date            
Displaying the Integer Value for Part of a Date Using YEAR, MONTH,and DAY                                                  
Type Conversion                                                   
Converting Between Data Types                                 
Converting Dates to Their Textual Representation                   
Representing Binary Data in String Literals                        
Evaluating the Data Type Returned by an Expression                
Ranking Functions                                                  
Generating an Incrementing Row Number                         
Returning Rows by Rank                                       
Returning Rows by Rank Without Gaps                            
Using NTILE                                                  
Probing Server, Database, and Connection-Level Settings Using System Functions                                                
Determining the First Day of the Week                            
Viewing the Language Used in the Current Session                 
Viewing and Setting Current Connection Lock Timeout Settings       
Displaying the Nesting Level for the Current Stored Procedure Context                                          
Returning the Current SQL Server Instance Name and SQL Server Version                                         
Returning the Current Connection’s Session ID (SPID)               
Returning the Number of Open Transactions                       
Retrieving the Number of Rows Affected by the
Previous Statement                                         
Retrieving System Statistics                                     
Displaying Database and SQL Server Settings                      
Returning the Current Database ID and Name                      
Returning a Database Object Name and ID                        
Returning the Application and Host for the Current User Session      
Reporting Current User and Login Context                         
Viewing User Connection Options                                
IDENTITY and uniqueidentifier Functions                               
Returning the Last Identity Value                                 
Returning an Identity Column’s Seed and Incrementing Value        
Creating a New uniqueidentifier Value                            

CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors    
Conditional Processing                                              
Using CASE to Evaluate a Single Input Expression                  
Using CASE to Evaluate Boolean Expressions                      
Using IFELSE                                                
Control-of-Flow                                                    
Using RETURN                                                
Using WHILE                                                  
Using GOTO                                                  
Using WAITFOR                                               
Cursors                                                           
Creating and Using Transact-SQL Cursors                         

CHAPTER 10 Stored Procedures                                            
Stored Procedure Basics                                             
Creating a Basic Stored Procedure                               
Creating a Parameterized Stored Procedure                       
Using OUTPUT Parameters                                      
Modifying a Stored Procedure                                   
Dropping Stored Procedures                                    
Executing Stored Procedures Automatically at SQL Server Startup     
Reporting Stored Procedure Metadata                            
Documenting Stored Procedures                                 
Stored Procedure Security                                           
Encrypting a Stored Procedure                                  
Using EXECUTE AS to Specify the Procedure’s Security Context       
Recompilation and Caching                                          
RECOMPILE(ing) a Stored Procedure Each Time It Is Executed        
Flushing the Procedure Cache                                   

CHAPTER 11 User-Defined Functions and Types                          
UDF Basics                                                        
Creating Scalar User-Defined Functions                           
Creating Inline User-Defined Functions                           
Creating Multi-Statement User-Defined Functions                  
Modifying User-Defined Functions                               
Viewing UDF Metadata                                         
Dropping User-Defined Functions                               
Benefitting from UDFs                                               
Maintaining Reusable Code                                    
Cross-Referencing Natural Key Values                          
Replacing Views with Multi-Statement UDFs                      
UDT Basics                                                      
Creating and Using User-Defined Types                          
Identifying Columns and Parameters with Dependencies
on User-Defined Types                                    
Dropping User-Defined Types                                  
Passing Table-Valued Parameters                               

CHAPTER 12 Triggers                                                       
DML Triggers                                                     
Creating an AFTER DML Trigger                                
Creating an INSTEAD OF DML Trigger                            
Handling Transactions Within DML Triggers                      
Controlling DML Triggers Based on Modified Columns              
Viewing DML Trigger Metadata                                 
DDL Triggers                                                     
Creating a DDL Trigger That Audits Database-Level Events          
Creating a DDL Trigger That Audits Server-Level Events            
Using a Logon Trigger                                         
Viewing DDL Trigger Metadata                                
Managing Triggers                                                
Modifying a Trigger                                          
Enabling and Disabling Table Triggers                           
Limiting Trigger Nesting                                       
Controlling Trigger Recursion                                  
Setting Trigger Firing Order                                   
Dropping a Trigger                                            

CHAPTER 13 CLR Integration                                              
CLR Overview                                                   
When (and When Not) to Use Assemblies                              
CLR Objects Overview                                             
Creating CLR Database Objects                                     
Enabling CLR Support in SQL Server                            
Writing an Assembly for a CLR Stored Procedure                  
Compiling an Assembly into a DLL File                           
Loading the Assembly into SQL Server                           
Creating the CLR Stored Procedure                              
Creating a CLR Scalar User-Defined Function                    
Creating a CLR Trigger                                        
Administering Assemblies                                          
Viewing Assembly Metadata                                   
Modifying an Assembly’s Permissions                           
Removing an Assembly from the Database                       

CHAPTER 14 XML, Hierarchies, and Spatial Data                       
Working with Native XML                                           
Creating XML Data Type Columns                             
Inserting XML Data into a Column                               
Validating XML Data Using Schemas                            
Retrieving XML Data                                          
Modifying XML Data                                          
Indexing XML Data                                           
Converting Between XML Documents and Relational Data                
Formatting Relational Data As XML                              
Converting XML to a Relational Form                           
Working with Native Hierarchical Data                                
Storing Hierarchical Data                                    
Returning a Specific Ancestor                                  
Returning Child Nodes                                        
Returning a Node’s Depth                                     
Returning the Root Node                                     
Determining Whether a Node Is a Child of the Current Node         
Changing Node Locations                                      
Native Spatial Data                                                
Storing Spatial Data                                          
Querying Spatial Data                                         

CHAPTER 15 Hints                                                          
Using Join Hints                                                   
Forcing a HASH Join                                          
Using Query Hints                                                 
Forcing a Statement Recompile                                 
Using Table Hints                                                 
Executing a Query Without Locking                              
Forcing a SEEK over a SCAN                                    

CHAPTER 16 Error Handling                                                
System-Defined and User-Defined Error Messages                      
Viewing System Error Information                               
Creating a User-Defined Error Message                          
Dropping a User-Defined Error Message                          
Manually Raising an Error                                           
Invoking an Error Message                                      
Trapping and Handling Application Errors                               
Old-Style Error Handling                                       
Error Handling with TRYCATCH                                
Applying Error Handling Without Recoding a Stored Procedure        
Nesting Error Handling                                         

CHAPTER 17 Principals                                                     
Windows Principals                                                 
Creating a Windows Login                                      
Viewing Windows Logins                                       
Altering a Windows Login                                       
Dropping a Windows Login                                     
Denying SQL Server Access to a Windows User or Group            
SQL Server Principals                                               
Creating a SQL Server Login                                    
Viewing SQL Server Logins                                     
Altering a SQL Server Login                                     
Managing a Login’s Password                                   
Dropping a SQL Login                                          
Managing Server Role Members                                 
Reporting Fixed Server Role Information                          
Database Principals                                                 
Creating Database Users                                       
Reporting Database User Information                             
Modifying a Database User                                     
Removing a Database User from the Database                     
Fixing Orphaned Database Users                                 
Reporting Fixed Database Roles Information                       
Managing Fixed Database Role Membership                       
Managing User-Defined Database Roles                          
Managing Application Roles                                     

CHAPTER 18 Securables, Permissions, and Auditing                     
Permissions Overview                                               
Reporting SQL Server Assignable Permissions                     
Server-Scoped Securables and Permissions                            
Managing Server Permissions                                   
Querying Server-Level Permissions                              
Database-Scoped Securables and Permissions                          
Managing Database Permissions                                
Querying Database Permissions                                 
Schema-Scoped Securables and Permissions                           
Managing Schemas                                            
Managing Schema Permissions                                  
Object Permissions                                                 
Managing Object Permissions                                   
Managing Permissions Across Securable Scopes                     
Determining a Current Connection’s Permissions to a Securable      
Reporting the Permissions for a Principal by Securable Scope       
Changing Securable Ownership                                 
Allowing SQL Logins to Access Non-SQL Server Resources         
Auditing SQL Instance and Database-Level Activity of Principals
Against Securables                                              
Defining Audit Data Sources                                   
Capturing SQL Instance–Scoped Events                           
Capturing Database-Scoped Events                              
Querying Captured Audit Data                                   
Managing, Modifying, and Removing Audit Objects                 

CHAPTER 19 Encryption                                                     
Encryption by Passphrase                                           
Using a Function to Encrypt by Passphrase                        
Master Keys                                                       
Backing Up and Restoring a Service Master Key                    
Creating, Regenerating, and Dropping a Database Master Key        
Backing Up and Restoring a Database Master Key                  
Removing Service Master Key Encryption from the Database Master Key                                                
Asymmetric Key Encryption                                          
Creating an Asymmetric Key                                    
Viewing Asymmetric Keys in the Current Database                  
Modifying the Asymmetric Key’s Private Key Password              
Encrypting and Decrypting Data Using an Asymmetric Key           
Dropping an Asymmetric Key                                    
Symmetric Key Encryption                                           
Creating a Symmetric Key                                      
Viewing Symmetric Keys in the Current Database                  
Changing How a Symmetric Key Is Encrypted                      
Using Symmetric Key Encryption and Decryption                   
Dropping a Symmetric Key                                      
Certificate Encryption                                               
Creating a Database Certificate                                  
Viewing Certificates in the Database                              
Backing Up and Restoring a Certificate                           
Managing a Certificate’s Private Key                              
Using Certificate Encryption and Decryption                       
Automatically Opening and Decrypting via a Symmetric Key          
Transparent Data Encryption                                         
Enabling Transparent Data Encryption                            
Managing and Removing TDE                                   

CHAPTER 20 Service Broker                                                 
Example Scenario: Online Bookstore                                   
Creating a Basic Service Broker Application                            
Enabling Databases for Service Broker Activity                     
Creating the Database Master Key for Encryption                   
Managing Message Types                                      
Creating Contracts                                             
Creating Queues                                              
Creating Services                                             
Initiating a Dialog                                              
Querying the Queue for Incoming Messages                       
Receiving and Responding to a Message                          
Ending a Conversation                                         
Prioritizing Service Broker Conversations                          
Creating a Stored Procedure to Process Messages                       
Creating the Bookstore Stored Procedure                         
Remote-Server Service Broker Implementations                         
Enabling Transport Security                                     
Enabling Dialog Security                                        
Creating Routes and Remote Service Bindings                     
Event Notifications                                                  
Capturing Login Commands                                     

CHAPTER 21 Configuring and Viewing SQL Server Options              
Viewing SQL Server Configurations                               
Changing SQL Server Configurations                             

CHAPTER 22 Creating and Configuring Databases                        
Creating, Altering, and Dropping Databases                             
Creating a Database with a Default Configuration                   
Viewing Database Information                                   
Creating a Database Using File Options                           
Creating a Database with a User-Defined Filegroup                 
Setting Database User Access                                   
Renaming a Database                                          
Dropping a Database                                          
Detaching a Database                                          
Attaching a Database                                          
Configuring Database Options                                        
Viewing Database Options                                      
Configuring ANSI SQL Options                                   
Configuring Automatic Options                                  
Creating or Modifying a Database to Allow External Access          
Creating or Changing a Database to Use a Non-Server Default Collation                                            
Configuring Cursor Options                                     
Enabling Date Correlation Optimization                           
Modifying Database Parameterization Behavior                    
Enabling Read Consistency for a Transaction                      
Configuring Database Recovery Models                           
Configuring Page Verification                                    
Controlling Database Access and Ownership                            
Changing a Database State to Online, Offline, or Emergency          
Changing a Database Owner                                    
Managing Database Files and Filegroups                               
Adding a Data File or Log File to an Existing Database               
Removing a Data or Log File from a Database                      
Relocating a Data or Transaction Log File                         
Changing a File’s Logical Name                                 
Increasing a Database’s File Size and Modifying Its Growth Options                                            
Adding a Filegroup to an Existing Database                        
Setting the Default Filegroup                                    
Removing a Filegroup                                          
Making a Database or Filegroup Read-Only                        
Viewing and Managing Database Space Usage                          
Viewing Database Space Usage                                 
Shrinking the Database or a Database File                        

CHAPTER 23 Database Integrity and Optimization                        
Database Integrity Checking                                         
Checking Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC                                    
Checking Allocation and Structural Integrity with DBCC CHECKDB     
Tables and Constraints                                              
Checking Allocation and Structural Integrity of All Tables in a Filegroup Using DBCC CHECKFILEGROUP                     
Checking Data Integrity for Tables and Indexed Views Using
DBCC CHECKTABLE                                         
Checking Table Integrity with DBCC CHECKCONSTRAINTS           
Checking System Table Consistency with DBCC CHECKCATALOG     
Index Maintenance                                                 
Rebuilding Indexes                                            
Defragmenting Indexes                                         
Rebuilding a Heap                                             

CHAPTER 24 Maintaining Database Objects and Object Dependencies                                         
Database Object Maintenance                                        
Changing the Name of a User-Created Database Object             
Changing an Object’s Schema                                   
Object Dependencies                                               
Identifying Object Dependencies                                 
Identifying Referencing and Referenced Entities                    
Viewing an Object’s Definition                                   

CHAPTER 25 Database Mirroring                                           
Database Mirroring in Context                                        
Database Mirroring Architecture                                      
Setting Up Database Mirroring                                        
Creating Mirroring Endpoints                                    
Backing Up and Restoring Principal Databases                     
Creating a Database Mirroring Session                            
Setup Summary                                                    
Operating Database Mirroring                                        
Changing Operating Modes                                     
Performing Failovers                                           
Pausing or Resuming a Mirroring Session                         
Stopping Mirroring Sessions and Removing Endpoints               
Monitoring and Configuring Options                                   
Monitoring Mirror Status                                        
Configuring the Connection Timeout Period                        

CHAPTER 26 Database Snapshots                                          
Snapshot Basics                                                   
Creating and Querying Database Snapshots                       
Removing a Database Snapshot                                 
Recovering Data with a Database Snapshot                        

CHAPTER 27 Linked Servers and Distributed Queries                    
Linked Server Basics                                                
Creating a Linked Server to Another SQL Server Instance            
Configuring Linked Server Properties                             
Viewing Linked Server Information                               
Dropping a Linked Server                                       
Linked Server Logins                                                
Adding a Linked Server Login Mapping                           
Viewing Linked Logins                                         
Dropping a Linked Server Login Mapping                         
Executing Distributed Queries                                       
Executing Distributed Queries Against a Linked Server              
Creating and Using an Alias to Reference Four-Part Linked Server Names                                              
Executing Distributed Queries Using OPENQUERY                   
Executing Ad Hoc Queries Using OPENROWSET                    
Reading Data from a File Using OPENROWSET BULK Options         
Chapter 28 Query Performance Tuning                                   
Query Performance Tips                                             
Capturing and Evaluating Query Performance                           
Capturing Executing Queries Using sysdm_exec_requests           
Viewing Estimated Query Execution Plans Using Transact-SQL Commands                                    
Viewing Execution Runtime Information                           
Viewing Performance Statistics for Cached Query Plans             
Viewing Aggregated Performance Statistics Based on Query or Plan Patterns                                            
Identifying the Top Bottleneck                                   
Identifying I/O Contention by Database and File                    
Index Tuning                                                      
Displaying Index Fragmentation                                 
Displaying Index Usage                                         
Statistics                                                          
Manually Creating Statistics                                     
Creating Statistics on a Subset of Rows                           
Updating Statistics                                            
Generating and Updating Statistics Across All Tables                
Viewing Statistics Details                                       
Removing Statistics                                            
Miscellaneous Techniques                                           
Using an Alternative to Dynamic SQL                             
Forcing SQL Server to Use a Query Plan                           
Applying Hints Without Modifying Application SQL                  
Creating Plan Guides from Cache                                
Checking the Validity of a Plan Guide                             
Parameterizing a Non-parameterized Query Using Plan Guides       
Limiting Competing Query Resource Consumption                  

CHAPTER 29 Backup and Recovery                                         
Creating a Backup and Recovery Plan                                 
Making Backups                                                   
Performing a Basic Full Backup                                  
Compressing Your Backups                                     
Naming and Describing Your Backups and Media                   
Configuring Backup Retention                                   
Striping Backup Sets                                           
Using a Named Backup Device                                  
Mirroring Backup Sets                                         
Performing a Transaction Log Backup                            
Create Backups Without Breaking the Backup Sequence             
Performing a Differential Backup                                 
Backing Up Individual Files or Filegroups                          
Performing a Partial Backup                                    
Viewing Backup Metadata                                      
Restoring a Database                                               
Restoring a Database from a Full Backup                         
Restoring a Database from a Transaction Log Backup               
Restoring a Database from a Differential Backup                   
Restoring a File or Filegroup                                    
Performing a Piecemeal (PARTIAL) Restore                        
Restoring a Page                                              
Identifying Databases with Multiple Recovery Paths

I came home to find a hard copy of my new book waiting for me.  This is always an exciting and strange moment to finally hold the finished product in my hand.  I’m pretty happy with the result, and I feel it reflects the problem/solution theme I was aiming for.  It is always a little daunting once a book is printed, because you have to hand it off to the world and hope that people find it useful.  You kind of have to just give in, hope for the best, and move on to the next project.

It is a bigger book than last time (last book was 733 pages, this one is 839).  Although it was an update - it still took 9 months, but they were high quality hours and I enjoyed a good portion of it.

Looking through it today -  I was disappointed to see that two of my requested changes that I submitted to the publisher in June didn’t get integrated into to Chapter 2 - so I’ll blog about them here so you can know to look for them.  There were some syntax changes that were made in the Release Candidate that differ from CTP 6.  The two changes that didn’t get in for whatever reason are as follows:

• Page 91 - the CTP6 version of the MERGE syntax, instead of the RC0 version was used.  Here is the corrected, RC0 version of the syntax:

MERGE INTO HumanResources.CorporateHousing p
USING dbo.StagingCorporateHousing s
ON p.UnitNBR = s.UnitNBR
WHEN MATCHED AND s.IsRentedIND <> p.IsRentedIND THEN
UPDATE SET IsRentedIND = s.IsRentedIND
WHEN NOT MATCHED BY TARGET THEN
INSERT (UnitNBR, IsRentedIND) VALUES (s.UnitNBR, s.IsRentedIND)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

• Page 98 - the @partition_switch argument should be removed from sys.sp_cdc_enable_table

The rest of the RC0 changes I made and requested seem to be there, so I’m happy.  We’ll see if there are any RTM changes I need to blog about.  With 800 pages - the possibilities are endless.

Problem: Install of a new SQL Server Failover Cluster is failing.  Looking in the Datastore.xml file shows a watsonFailedFunction = “ClusterInfoCollector::collectClusterGroups.”

Resolution: This particular failed function is often associated with one or more cluster resources being offline or failed during a SQL Server install.  In the case I worked on, there was a bad Veritas cluster disk resource.  They removed the resource, and the install succeeded.

I was reading an article the other day that detailed how to collect file usage across user databases using the sys.dm_db_file_space_usage DMV.   I did a double-take, because that DMV only applies to the tempdb system database.   In all fairness and  based on the name of this DMV, it would be easy to think this applies to all databases.   Perhaps it was named as it was (in a more generic format) in preparation for expanding to other databases in a future version (as of SQL Server 2008, it still just applies to tempdb).   Today the appropriate name should be something more like sys.dm_db_tempdb_file_space_usage…

 So what could you use instead, to detect free space usage for a user database? I like to use the undocumented DBCC SHOWFILESTATS command. It is pretty straight-forward, returning the file id, file group id,  total extents, used extents, logical  file name and physical file name.   If you are worried about using something undocumented,  in this case  wouldn’t  be too concerned as this is the same command used in SQL Server Management Studio when you run the “Disk Usage” report.  

Oh and by the way - if you are looking for transaction log file space usage statistics, you can use the undocumented DBCC SQLPERF(LOGSPACE) command.   Again, this is being used under the covers for the “Disk Usage” SQL Server Management Studio reports.

Problem:

User was trying to CREATE LOGIN for a Windows Domain account, [DG\example] on a case sensitive SQL Server instance and was seeing error message 15401, “Windows NT user or group ‘%s’ not found. Check the name again.” This was on a four node cluster.   Checking one of the nodes, we found that the failing account was actually added as a member of the local admin group using upper-case (so it was created as DG\EXAMPLE).   Looking in Active Directory however, the account was actually DG\example, lower-case.   So even though we are trying to add the login to SQL Server with the correct lower-case, it looked like SQL Server was doing a local lookup based on the local node’s version of the name, which was upper-case.   I used SUSER_SID to validate each version - lower case and upper case, and only the upper case version of the account worked.

Solution:

To resolve this issue, I followed the instructions in KB 946358, “The LsaLookupSids function may return the old user name instead of the new user name if the user name has changed on a domain controller.”   After creating the registry entry and setting the value to “0″, the CREATE LOGIN of the account with the proper case  was successful.   I then removed the registry value to return to the default SID cache behavior (default maximum number is 128).

Now that I am finished with the book, I’m looking forward to getting out into the community a little bit more.   Being an introverted, behind-the-scenes kind of guy, I am  sorry to say that I have never attended a PASS Minnesota SQL Server User group meeting.  This is about to chance, as  Lara Rubbelke  was kind enough to invite me to join the “SQL Server Ask the Experts” panel coming up on July 15th.    Should be fun to meet everyone.   Each panelist will give a 5 minute tip or trick in his/her subject area - and then the rest of the time will be spent answering questions from the attendees.   Those of you SQL Server professionals that live in the Twin Cities, please register for the event and stop by to say hello!

My new book, “SQL Server 2008 Transact-SQL Recipes” is coming out in a few weeks (estimated publication at the end of July). As of July 1^st, Amazon.com is selling it for $37.70. They don’t have a picture of the cover up yet, but it will have the standard bumble-bee color scheme Apress cover, so expect no surprises there. J

Although this was an update of my last book, it still took 9 months to write (plenty of weeknights and weekends holed up in my office).   I updated existing content, and added content for both SQL Server 2008 and SQL Server 2005 SP2 additions.

Like my last book, I focus specifically on the Transact-SQL language. Even with the focus on T-SQL and not the GUI, the book wound up being greater than 800 pages.   Also like the last book, I use a problem/solution format.   The overall theme and mission statement for this book was:

 “Look up what you need to do. Learn how to do it. Do it.”  

I’ll have a downloadable detailed index link available after the publication date.   That link will include the specific recipes for each chapter.   In the meantime, here is a high level list of the chapters:

CHAPTER 1 SELECT

CHAPTER 2 Perform, Capture, and Track Data Modifications

CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking

CHAPTER 4 Tables

CHAPTER 5 Indexes

CHAPTER 6 Full-Text Search

CHAPTER 7 Views

CHAPTER 8 SQL Server Functions

CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors

CHAPTER 10 Stored Procedures

CHAPTER 11 User-Defined Functions and Types

CHAPTER 12 Triggers

CHAPTER 13 CLR Integration

CHAPTER 14 XML, Hierarchies, and Spatial Data

CHAPTER 15 Hints

CHAPTER 16 Error Handling

CHAPTER 17 Principals

CHAPTER 18 Securables, Permissions, and Auditing

CHAPTER 19 Encryption

CHAPTER 20 Service Broker

CHAPTER 21 Configuring and Viewing SQL Server Options

CHAPTER 22 Creating and Configuring Databases

CHAPTER 23 Database Integrity and Optimization

CHAPTER 24 Maintaining Database Objects and Object Dependencies

CHAPTER 25 Database Mirroring

CHAPTER 26 Database Snapshots

CHAPTER 27 Linked Servers and Distributed Queries

CHAPTER 28 Query Performance Tuning

CHAPTER 29 Backup and Recovery

I enjoyed writing this book; but most importantly I hope you find it to be a useful, practical reference.  

Quick post today - saw a question on how you find the certificate that was used to encrypt the Database Encryption Key (DEK) used for a Transparent Data Encrypted database.   Here it is:

USE master
GO

SELECT c.name, c.certificate_id
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys x ON
        c.thumbprint = x.encryptor_thumbprint

As you may already be aware, the ASYNC_NETWORK_IO (seen in SQL 2005) and NETWORKIO (seen in SQL 2000) wait types are associated with either a calling application that is not processing results quickly enough from SQL Server or is associated with a network performance issue.

As I received a question on this today, I thought I would share prescriptive guidance on what to do if you see ASYNC_NETWORK_IO or NETWORKIO wait types:

- Identify large result sets and verify with the application team (or developers) how this is being consumed.   Red flags include the application querying large results sets but not processing more than a few rows at a time

- Ensure that the client application processes all rows it is requesting.   If not all rows will be needed or used - consider only querying the rows that are needed (TOP X for example)

- Look at your NIC configuration on the server and make sure there are no issues (physical card issue, autodetect not picking the fastest speed)

- Validate the network components between the application/clients and the SQL Server instance (router, for example)

As a side note - if you are doing server-side data loads that are still showing NETWORKIO, check to see if shared memory protocol is enabled for the SQL Server instance (and then check to see if session is connected using net_transport = ‘Shared memory” via sys.dm_exec_connections).  

Today I had reason to install the Cumulative update package 6 for SQL Server 2005 Service Pack 2 in order to address a specific bug.   After installing it, an end-user encountered errors when trying to expand the table tree view under a database in SQL Server Management Studio.   The result is that she couldn’t see individual database objects under DBName\Tables, DBName\Views, etc.

I checked her version of SQL Server Management Studio (using Help/About).   I saw that this was pre-SP2, so we uploaded and installed SP2 for her client tools, and that resolved the issue.   So moral of the story is that when upgrading the Server engine, you need to also  consider upgrading your client tools.