List of Listings

Chapter 2. SQL Server tools for maintaining data integrity

Listing 1. Basic template for triggers

Listing 2. Trigger to ensure line item total > 0

Chapter 4. Set-based iteration, the third alternative

Listing 1. Set-based iteration with the TOP clause

Listing 2. Declarative code for calculating running totals

Listing 3. Iterative code for calculating running totals

Listing 4. Set-based iteration for calculating running totals

Listing 5. Set up tables and generate random data for bin packing

Listing 6. Iterative code for bin packing

Listing 7. Creating the numbers table for use in the set-based bin-packing code

Listing 8. Set-based iteration for bin packing

Chapter 5. Gaps and islands

Listing 1. Code creating and populating table NumSeq

Listing 2. Code creating and populating the BigNumSeq table

Listing 3. Gaps—solution 1 using subqueries

Listing 4. Gaps—solution 2 using subqueries

Listing 5. Gaps—solution 3 using ranking functions

Listing 6. Gaps—solution 4 using cursors

Listing 7. Islands—solution 1 using subqueries and ranking calculations

Listing 8. Islands—solution 2 using group identifier based on subqueries

Listing 9. Islands—solution 3 using group identifier based on ranking calculations

Listing 10. Islands—solution 4 using cursors

Listing 11. Code creating and populating table T1

Listing 12. Solution to variation on the islands problem

Chapter 6. Error handling in SQL Server and applications

Listing 1. Error sent to SQL Server Management Studio

Listing 2. T-SQL statements in a TRY...CATCH block

Listing 3. Outputting error properties with system-provided functions

Listing 4. ERROR_LINE and ERROR_PROCEDURE functions in a stored procedure

Listing 5. Returning user-created error messages with RAISERROR

Listing 6. Nesting TRY...CATCH blocks

Listing 7. Error handling with nested TRY...CATCH statements

Listing 8. An error-handling module

Listing 9. Transaction processing in a TRY...CATCH block

Listing 10. Outputting SQL Server–specific error properties with SqlException

Listing 11. Handling multiple errors with the Errors property

Listing 12. Outputting SQL Server messages

Listing 13. Capturing RAISERROR statements

Chapter 7. Pulling apart the FROM clause

Listing 1. Query to return rows with matching product subcategories

Listing 2. A LEFT OUTER JOIN

Listing 3. Beware of COUNT(*) with OUTER JOINs

Listing 4. Using a CROSS JOIN to cover all combinations

Listing 5. A FROM clause from the Timesheet Audit Report

Listing 6. A reformatted version of the FROM clause in listing 5

Listing 7. Placing a predicate in the ON clause of an outer join

Listing 8. View to return products and their subcategories

Listing 9. View to return all products and their subcategories (if they exist)

Listing 10. Query to return products and their subcategory

Listing 11. Using a FULL JOIN

Chapter 8. What makes a bulk insert a minimally logged operation?

Listing 1. SQL scripts to create databases

Listing 2. Error message on running bcp utility from a query window

Listing 3. Script to enable xp_cmdshell

Listing 4. Script to create a database table in six different databases

Listing 5. BULK INSERT statements to import data without the TABLOCK hint

Listing 6. BULK INSERT statement to import data with TABLOCK hint

Listing 7. Query to determine the size of log files

Chapter 9. Avoiding three common query mistakes

Listing 1. Three queries to include NULL

Listing 2. The code to create the Production.ColorList table

Listing 3. Query returns no rows because of NULL values in the subquery

Listing 4. The correct code to find the list of unused colors

Listing 5. How to return all rows even if there isn’t a match

Listing 6. The same results are returned when using a RIGHT OUTER JOIN.

Listing 7. Non-matching rows lost when INNER JOIN follows LEFT OUTER JOIN.

Listing 8. Using LEFT OUTER JOIN down the OUTER JOIN path

Listing 9. Using a RIGHT OUTER JOIN followed by a LEFT OUTER JOIN

Listing 10. Missing the GROUP BY clause

Listing 11. An extra column in the GROUP BY clause

Listing 12. This query runs, but the results are invalid.

Listing 13. Writing the query so that the expression is used in the GROUP BY clause

Chapter 10. Introduction to XQuery on SQL Server

Listing 1. Simple XML document

Listing 2. XML with multiple instances of the same element at the same level

Listing 3. Sample employee XML content

Listing 4. Querying XML data

Listing 5. Retrieving a single scalar value

Listing 6. Confirming existence of a node

Listing 7. Shredding XML with the .nodes() method

Listing 8. Querying XML with a FLWOR expression

Listing 9. Binding tuples to variables with the let clause

Listing 10. Sorting tuples with the order by clause

Listing 11. Restricting results with the where clause

Listing 12. Comparing a sequence with a single value to a scalar value

Listing 13. Comparing with the value comparison operators

Listing 14. Comparing sequences with general comparison operators

Listing 15. Comparing nodes with the node comparison operators

Chapter 11. SQL Server XML frequently asked questions

Listing 1. Sample XML document

Listing 2. Extracting state elements from XML document

Listing 3. Sample prolog

Listing 4. Sample prolog with encoding specifier

Listing 5. Converting XML with a DTD

Listing 6. Applying single-byte encoding to Unicode characters

Listing 7. Eliminating single-byte-to-Unicode conversion problems

Listing 8. Invalid single-byte-to-Unicode conversion

Listing 9. Avoiding single-byte-to-Unicode conversion problems

Listing 10. Querying XML data

Listing 11. Retrieving a single scalar value from XML

Listing 12. Shredding XML data

Listing 13. Shredding XML with OPENXML

Listing 14. Sample XML with namespaces

Listing 15. Querying XML with namespaces

Listing 16. Using WITH XMLNAMESPACES clause

Listing 17. Retrieving all element names and values from XML

Listing 18. Sample state-list.xml file

Listing 19. Loading XML data from the filesystem

Chapter 12. Using XML to transport relational data

Listing 1. The Album XML Schema

Listing 2. The Band XML Schema

Listing 3. Common XML Schema

Listing 4. Extracting the titles

Listing 5. Simplified query with union XPath expression

Listing 6. Extracting the albums

Listing 7. Extracting the tracks

Listing 8. Extracting the persons

Listing 9. Extracting the bands

Listing 10. Extracting the bands

Listing 11. Extracting the persons

Listing 12. To export the Band data from the database

Listing 13. To export the Album data from the database

Chapter 13. Full-text searching

Listing 1. Real-world example using FREETEXTTABLE

Listing 2. Default thesaurus XML file

Listing 3. Full information about tables and full-text searching

Listing 4. List all columns that are full-text indexed

Listing 5. Using FullTextCatalogProperty to get information

Listing 6. Determining the number of fragments for your full-text indexes

Chapter 14. Simil: an algorithm to look for similar strings

Listing 1. Calling the fnSimil() function from a stored procedure

Listing 2. Using the fnSimil() function to search an entire table

Listing 3. Comparing Simil values between a .NET assembly and a classic DLL

Chapter 16. Table-valued parameters

Listing 1. Code to use a DataTable to pass a TVP to a stored procedure

Listing 2. Using a DbDataReader object as a TVP

Listing 3. Code to create database objects to insert orders

Listing 4. Checkout code that creates the order in the database

Chapter 17. Build your own index

Listing 1. Creating the persons table and index on email

Listing 2. The procedure map_search_five

Listing 3. The trigger keeps fragment_persons updated.

Listing 4. Loading the fragments_personlists table

Listing 5. Search procedure using fragments_personlists

Listing 6. Filtering out unchanged fragment-person_id mappings

Listing 7. Search function using the bitmask

Chapter 18. Getting and staying connected—or not

Listing 1. Testing for network availability in Visual Basic.NET

Listing 2. Starting SQL Server and supporting services in a command batch

Listing 3. Capturing the list of visible SQL Server instances

Listing 4. Starting a selected service

Chapter 19. Extending your productivity in SSMS and Query Analyzer

Listing 1. Creating sample utility sp_getcolumns

Chapter 20. Why every SQL developer needs a tools database

Listing 1. Script to create a numbers table

Listing 2. Query to create dates from the numbers table

Listing 3. Abridged result set of dates created from the numbers table.

Listing 4. Query to create dates in the past from the numbers table

Listing 5. Abridged result set of dates created in the past from the numbers table

Listing 6. Query to return the first and last day of every quarter from 2000 to 2024

Listing 7. Abridged result set of query in listing 6

Listing 8. Stored procedure to split delimited strings with the numbers table

Listing 9. Result set of unique characters in a string

Listing 10. Stored procedure with comments

Listing 11. Table for state tax rates

Listing 12. User-defined function to calculate tax

Listing 13. Four example calls and their results

Listing 14. Function to format a date

Chapter 21. Deprecation feature

Listing 1. Reading the SQLServer:Deprecated Features counters

Listing 2. Event notification method

Listing 3. Extended Events method

Listing 4. XML result

Chapter 22. Placing SQL Server in your pocket

Listing 1. Bypassing the query processor

Chapter 23. Mobile data strategies

Listing 1. WCF service IService.cs

Listing 2. WCF service.cs

Listing 3. WCF service App.config

Listing 4. Mobile console app TestSync.cs

Listing 5. Mobile class SyncClient

Chapter 26. PowerShell in SQL Server

Listing 1. Accessing SQL Server data with PowerShell

Listing 2. Accessing SMO with PowerShell

Listing 3. Accessing data with PowerShell cmdlet

Chapter 27. Automating SQL Server Management using SMO

Listing 1. Code to load SMOExtended.dll and SQLWMIManagement.dll

Listing 2. Backing up user databases

Listing 3. Restoring a copy of an existing database from backup

Listing 4. Creating a user database

Listing 5. Scripting all objects in the AdventureWorks database

Chapter 28. Practical auditing in SQL Server 2008

Listing 1. Creating a server audit using the Security Log target

Listing 2. Creating a server audit using the Application Log target

Listing 3. Creating a server audit using the File target

Listing 4. Creating a server audit specification using the Application Log target

Listing 5. Creating events for the server audit specification

Listing 6. Viewing audit events from T-SQL

Listing 7. Viewing details of all server audit specifications

Listing 8. Creating a server audit for a database audit specification

Listing 9. Creating a database audit specification to audit DML activity

Listing 10. Creating DML activity

Listing 11. Viewing DML activity audit events in File target

Listing 12. Additional examples of database audit specifications

Chapter 29. My favorite DMVs, and why

Listing 1. Sample query against sys.dm_db_index_physical_stats

Listing 2. Creating a utility database

Listing 3. An sp_who2 replacement

Listing 4. Table-valued function for procedure statistics

Listing 5. Finding unused stored procedures

Listing 6. Making a system stored procedure

Listing 7. Measuring the usefulness of indexes

Listing 8. Finding inefficient queries

Listing 9. Finding missing indexes

Chapter 30. Reusing space in a table

Listing 1. Creating and populating the dbo.Test table

Listing 2. Querying the dbo.Test table

Listing 3. Examining the space used by the dbo.Test table

Listing 4. Deleting the odd-numbered rows

Listing 5. Adding new rows to the dbo.Test table

Listing 6. Dropping the dbo.Test table

Listing 7. Creating the dbo.Test2 table

Listing 8. Dropping a varchar column in the dbo.Test table

Listing 9. Adding more rows to the dbo.Test2 table

Listing 10. Reclaiming space using DBCC CLEANTABLE

Chapter 32. Partitioning for manageability (and maybe performance)

Listing 1. Creating a partitioned sales transaction table

Figure 11. SQL Server 2008 partitioned index seek predicate including partition range expression

Chapter 33. Efficient backups without indexes

Listing 1. Scripting CREATE INDEX commands to a new filegroup

Chapter 34. Using database mirroring to become a superhero!

Listing 1. Script to create a login with identical SID on the mirror instance

Listing 2. Queries to monitor database mirroring

Chapter 35. The poor man’s SQL Server log shipping

Listing 1. T-SQL script (backupLog.sql) generates transaction log backups

Listing 2. VBScript (deleteTRN.vbs) cleans up the transaction log backups

Listing 3. Batch file (databaseBackupLog.cmd) calls the backup and cleanup scripts

Listing 4. Batch file (logShipRobocopy.cmd) calls the replication and restore scripts

Listing 5. Batch file (restoreLOG.vbs) calls the backup and cleanup scripts

Listing 6. VBScript file (sendEmailSMTP.vbs) sends email notifications

Listing 7. PowerShell translation of deleteTRN.vbs

Chapter 38. Successfully implementing Kerberos delegation

Listing 1. Finding out the SPNs that are currently in place

Chapter 40. When is an unused index not an unused index?

Listing 1. Querying the DMV to review the indexes and the execution plan

Chapter 41. Speeding up your queries with index covering

Listing 1. Restore the AdventureWorks database to AdventureWorks_Copy

Listing 2. Creating a noncovering index

Listing 3. Running two range queries

Listing 4. Comparing covering and noncovering index performance

Listing 5. Update statement performance improved with a covering index

Listing 6. Creating two covering indexes

Chapter 42. Tracing the deadlock

Listing 1. A sample deadlock graph

Chapter 44. Does the order of columns in an index matter?

Listing 1. A sample Customers table

Listing 2. Creating indexes for the Customers table

Listing 3. Finding a specific Customer row by Last_Name, First_Name

Listing 4. Finding a specific Customer row by First_Name, Last_Name

Listing 5. Finding customers by Last_Name

Listing 6. Finding Customers with a first name of Jake

Listing 7. Turning STATISTICS IO on

Listing 8. Using DBCC to drop the procedure cache and free memory

Listing 9. Dropping the ix_Customer_Name index

Chapter 46. Using correlation to improve query performance

Listing 1. Query to select WorkOrders for a 2-day date range

Listing 2. Query to select all WorkOrders except for 2 days

Listing 3. Query to select WorkOrders for a one-month date range

Listing 4. Using an index hint

Chapter 47. How to use Dynamic Management Views

Listing 1. Creating a monitoring login with view server state permission

Listing 2. Top waits query

Listing 3. Checking CPU pressure

Listing 4. Checking the runnable tasks count

Listing 5. Finding the most expensive stored procedures

Listing 6. Finding expensive stored procedures, sorted by average worker time

Listing 7. Checking for I/O pressure

Listing 8. Identifying the highest I/O waits

Listing 9. Checking I/O statistics for a database

Listing 10. Locating physical read I/O pressure

Listing 11. Finding stored procedures with the most write activity

Listing 12. Top 10 consumers of memory from buffer pool

Listing 13. Getting query mix and use counts for each plan

Listing 14. Finding indexes and tables that use the most buffer space

Listing 15. Finding ad hoc queries that are bloating the plan cache

Listing 16. Finding your 25 most expensive queries

Listing 17. Finding tables with the most reads

Listing 18. Finding tables with the most writes

Listing 19. Finding bad indexes

Listing 20. Looking at Index Advantage to find missing indexes

Listing 21. Looking at Last User Seek to find missing indexes

Listing 22. Getting statistics for a table

Listing 23. Missing indexes for a single table

Listing 24. Checking SQL Server schedulers to see if you may have blocking

Listing 25. Detecting blocking

Listing 26. Detecting blocking (a more accurate and complete version)

Listing 27. Looking at locks that are causing problems

Chapter 48. Query performance and disk I/O counters

Listing 1. Creating a test table and populating it with 4 million rows

Listing 2. Output from running DBCC SHOWCONTIG on the test table

Chapter 49. XEVENT: the next event infrastructure

Listing 1. Using Extended Events to monitor long-running queries

Listing 2. Output from running listing 1

Listing 3. Using sys.dm_xe_packages to list packages

Listing 4. Output from running listing 3

Listing 5. Enumerating Extended Events targets

Listing 6. Output from running listing 5

Listing 7. Generating a user dump

Listing 8. Output from running listing 7

Listing 9. Querying sys.dm_xe_sessions to determine oldest session

Listing 10. Getting the system health session active events and filters

Listing 11. Obtaining information from the system health session

Chapter 52. Reporting Services tips and tricks

Listing 1. Queries to compare performance of OPENQUERY and four-part naming

Listing 2. Editing rsreportserver.config to render CSV in ASCII

Listing 3. Script to change URL to include parameters

Listing 4. Macro to add date parameters and refresh the query

Listing 5. CSV alternative to web query wizards

Chapter 53. SQL Server Audit, change tracking, and change data capture

Listing 1. Preparing Employees schema and data

Listing 2. Creating a server and database audit

Listing 3. Updating the Employees table

Listing 4. Event log entry for the UPDATE command in listing 3

Listing 5. Creating a Server Audit with the SUCCESSFUL_LOGIN_GROUP

Listing 6. Cleaning up the audit specification

Listing 7. Enabling change tracking

Listing 8. Determining (and updating) the baseline version of a table

Listing 9. Retrieving changes to the Employees table

Listing 10. Using WITH CHANGE_TRACKING_CONTEXT() in an INSTEAD OF trigger

Listing 11. Calling the CHANGETABLE function

Listing 12. Disabling change tracking

Listing 13. Enabling a database and table for change tracking

Listing 14. Inserting data into the Employees table

Listing 15. Query against (and results from) a change data capture function

Listing 16. Using the get_net_changes function

Listing 17. Viewing the before and after image of each key row

Listing 18. Cleaning up change data capture settings

Chapter 56. Incorporating data profiling in the ETL process

Listing 1. Data profile XML prior to making it dynamic

Listing 2. Data profiling XML after converting to an expression

Listing 3. Script component to check column values against a list of patterns

Chapter 59. Incremental loads using T-SQL and SSIS

Listing 1. Creating the tblSource source

Listing 2. Creating the tblDest destination

Listing 3. Loading data

Listing 4. Viewing new rows

Listing 5. Incrementally loading new rows

Listing 6. Isolating changed rows

Listing 7. Updating the data

Listing 8. Resetting the tables