Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Wayne Sheffield, Jonathan Gennick, Jason Brimhall
SQL Server T-SQL Recipes, Fourth Edition
Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
About the Authors
About the Techincal reviewer
Acknowledgments
Introduction
Chapter 1: Getting Started with SELECT
1-1. Connecting to a Database
Problem
Solution
How It Works
1-2. Checking the Database Server Version
Problem
Solution
How It Works
1-3. Checking the Database Name
Problem
Solution
How It Works
1-4. Checking Your Username
Problem
Solution
How It Works
1-5. Querying a Table
Problem
Solution
How It Works
1-6. Returning Specific Rows
Problem
Solution
How It Works
1-7. Listing the Available Tables
Problem
Solution
How It Works
1-8. Naming the Output Columns
Problem
Solution
How It Works
1-9. Providing Shorthand Names for Tables
Problem
Solution
How It Works
1-10. Computing New Columns from Existing Data
Problem
Solution
How It Works
1-11. Negating a Search Condition
Problem
Solution
How It Works
1-12. Keeping the WHERE Clause Unambiguous
Problem
Solution
How It Works
1-13. Testing for Existence
Problem
Solution
How It Works
1-14. Specifying a Range of Values
Problem
Solution
How It Works
1-15. Checking for Null Values
Problem
Solution
How It Works
1-16. Writing an IN-List
Problem
Solution
How It Works
1-17. Performing Wildcard Searches
Problem
Solution
How It Works
1-18. Sorting Your Results
Problem
Solution
How It Works
1-19. Specifying the Case-Sensitivity of a Sort
Problem
Solution
How It Works
1-20. Sorting Nulls High or Low
Problem
Solution
How It Works
1-21. Forcing Unusual Sort Orders
Problem
Solution
How It Works
1-22. Paging Through a Result Set
Problem
Solution
How It Works
1-23. Sampling a Subset of Rows
Problem
Solution
How It Works
Chapter 2: Elementary Programming
2-1. Executing T-SQL from a File
Problem
Solution
How It Works
2-2. Retrieving Values into Variables
Problem
Solution
How It Works
2-3. Writing Expressions
Problem
Solution
How It Works
2-4. Deciding Between Two Execution Paths
Problem
Solution
How It Works
2-5. Detecting Whether Rows Exist
Problem
Solution
How It Works
2-6. Going to a Label in a Transact-SQL Batch
Problem
Solution
How It Works
2-7. Trapping and Throwing Errors
Problem
Solution
How It Works
2-8. Returning from the Current Execution Scope
Problem
Solution #1: Exit with No Return Value
Solution #2: Exit and Provide a Value
How It Works
2-9. Writing a Simple CASE Expression
Problem
Solution
How It Works
2-10. Writing a Searched CASE Expression
Problem
Solution
How It Works
2-11. Repeatedly Executing a Section of Code
Problem
Solution
How It Works
2-12. Controlling Iteration in a Loop
Problem
Solution
How It Works
2-13. Pausing Execution for a Period of Time
Problem
Solution
How It Works
2-14. Looping through Query Results a Row at a Time
Problem
Solution
How It Works
Chapter 3: Working with NULLS
3-1. Replacing NULL with an Alternate Value
Problem
Solution
How It Works
3-2. Returning the First Non-NULL Value from a List
Problem
Solution
How It Works
3-3. Choosing Between ISNULL and COALESCE in a SELECT Statement
Problem
Solution
How It Works
3-4. Looking for NULLs in a Table
Problem
Solution
How It Works
3-5. Removing Values from an Aggregate
Problem
Solution
How It Works
3-6. Enforcing Uniqueness with NULL Values
Problem
Solution
How It Works
3-7. Enforcing Referential Integrity on Nullable Columns
Problem
Solution
How It Works
3-8. Joining Tables on Nullable Columns
Problem
Solution
How It Works
Chapter 4: Querying from Multiple Tables
4-1. Correlating Parent and Child Rows
Problem
Solution
How It Works
4-2. Querying Many-to-Many Relationships
Problem
Solution
How It Works
4-3. Making One Side of a Join Optional
Problem
Solution
How It Works
4-4. Making Both Sides of a Join Optional
Problem
Solution
How It Works
4-5. Generating All Possible Row Combinations
Problem
Solution
How It Works
4-6. Selecting from a Result Set
Problem
Solution
How It Works
4-7. Introducing New Columns
Problem
Solution
How It Works
4-8. Testing for the Existence of a Row
Problem
Solution
How It Works
4-9. Testing Against the Result from a Query
Problem
Solution
How It Works
4-10. Stacking Two Row Sets Vertically
Problem
Solution
How It Works
4-11. Eliminating Duplicate Values from a Union
Problem
Solution
How It Works
4-12. Subtracting One Row Set from Another
Problem
Solution
How It Works
4-13. Finding Rows in Common Between Two Row Sets
Problem
Solution
How It Works
4-14. Finding Rows that Are Missing
Problem
Solution
How It Works
4-15. Comparing Two Tables
Problem
Solution
How It Works
Chapter 5: Aggregations and Grouping
5-1. Computing an Aggregation
Problem
Solution
How It Works
5-2. Creating Aggregations Based upon the Values of the Data
Problem
Solution
How It Works
5-3. Counting the Rows in a Group
Problem
Solution
How It Works
5-4. Detecting Changes in a Table
Problem
Solution
How It Works
5-5. Restricting a Result Set to Groups of Interest
Problem
Solution
How It Works
5-6. Performing Aggregations against Unique Values Only
Problem
Solution
How It Works
5-7. Creating Hierarchical Summaries
Problem
Solution
How It Works
5-8. Creating Summary Totals and Subtotals
Problem
Solution
How It Works
5-9. Creating Custom Summaries
Problem
Solution
How It Works
5-10. Identifying Rows Generated by the GROUP BY Arguments
Problem
Solution
How It Works
5-11. Identifying Summary Levels
Problem
Solution
How It Works
Chapter 6: Advanced Select Techniques
6-1. Avoiding Duplicate Results
Problem
Solution #1
Solution #2
How It Works
6-2. Returning the Top N Rows
Problem
Solution
How It Works
6-3. Renaming a Column in the Output
Problem
Solution
How It Works
6-4. Retrieving Data Directly into Variables
Problem
Solution
How It Works
6-5. Creating a New Table with the Results from a Query
Problem
Solution
How It Works
6-6. Filtering the Results from a Subquery
Problem
Solution
How It Works
6-7. Selecting from the Results of Another Query
Problem
Solution
How It Works
6-8. Passing Rows Through a Function
Problem
Solution
How It Works
6-9. Returning Random Rows from a Table
Problem
Solution
How It Works
6-10. Converting Rows into Columns
Problem
Solution
How It Works
6-11. Converting Columns into Rows
Problem
Solution
How It Works
6-12. Reusing Common Subqueries in a Query
Problem
Solution
How It Works
6-13. Querying Recursive Tables
Problem
Solution
How It Works
6-14. Hard-Coding the Results from a Query
Problem
Solution
How It Works
Chapter 7: Windowing Functions
7-1. Calculating Totals Based upon the Prior Row
Problem
Solution
How It Works
7-2. Calculating Totals Based upon a Subset of Rows
Problem
Solution
How It Works
7-3. Calculating a Percentage of Total
Problem
Solution
How It Works
7-4. Calculating a “Row X of Y”
Problem
Solution
How It Works
7-5. Using a Logical Window
Problem
Solution
How It Works
7-6. Generating an Incrementing Row Number
Problem
Solution
How It Works
7-7. Returning Rows by Rank
Problem
Solution
How It Works
7-8. Sorting Rows into Buckets
Problem
Solution
How It Works
7-9. Grouping Logically Consecutive Rows Together
Problem
Solution
How It Works
7-10. Accessing Values from Other Rows
Problem
Solution
How It Works
7-11. Finding Gaps in a Sequence of Numbers
Problem
Solution
How It Works
7-12. Accessing the First or Last Value from a Partition
Problem
Solution
How It Works
7-13. Calculating the Relative Position or Rank of a Value within a Set of Values
Problem
Solution
How It Works
7-14. Calculating Continuous or Discrete Percentiles
Problem
Solution
How It Works
7-15. Assigning Sequences in a Specified Order
Problem
Solution
How It Works
Chapter 8: Inserting, Updating, Deleting
8-1. Inserting a New Row
Problem
Solution
How It Works
8-2. Specifying Default Values
Problem
Solution
How It Works
8-3. Overriding an IDENTITY Column
Problem
Solution
How It Works
8-4. Generating a Globally Unique Identifier (GUID)
Problem
Solution
How It Works
8-5. Inserting Results from a Query
Problem
Solution
How It Works
8-6. Inserting Results from a Stored Procedure
Problem
Solution
How It Works
8-7. Inserting Multiple Rows at Once from Supplied Values
Problem
Solution
How It Works
8-8. Inserting Rows and Returning the Inserted Rows
Problem
Solution
How It Works
8-9. Updating a Single Row or Set of Rows
Problem
Solution
How It Works
8-10. Updating While Using a Second Table as the Data Source
Problem
Solution
How It Works
8-11. Updating Data and Returning the Affected Rows
Problem
Solution
How It Works
8-12. Updating Large-Value Columns
Problem
Solution
How It Works
8-13. Deleting Rows
Problem
Solution
How It Works
8-14. Deleting Rows and Returning the Deleted Rows
Problem
Solution
How It Works
8-15. Deleting All Rows Quickly (Truncating)
Problem
Solution
How It Works
8-16. Merging Data (Inserting, Updating, and/or Deleting Values)
Problem
Solution
How It Works
8-17. Inserting Output Data
Problem
Solution
How It Works
Chapter 9: Working with Strings
9-1. Concatenating Multiple Strings
Problem/+
Solution
How It Works
9-2. Finding a Character’s ASCII Value
Problem
Solution
How It Works
9-3. Returning Integer and Character Unicode Values
Problem
Solution
How It Works
9-4. Locating Characters in a String
Problem
Solution
How It Works
9-5. Determining the Similarity of Strings
Problem
Solution
How It Works
9-6. Returning the Leftmost or Rightmost Portion of a String
Problem
Solution
How It Works
9-7. Returning Part of a String
Problem
Solution
How It Works
9-8. Counting Characters or Bytes in a String
Problem
Solution
How It Works
9-9. Replacing Part of a String
Problem
Solution
How It Works
9-10. Stuffing a String into a String
Problem
Solution
How It Works
9-11. Changing Between Lowercase and Uppercase
Problem
Solution
How It Works
9-12. Removing Leading and Trailing Blanks
Problem
Solution
How It Works
9-13. Repeating an Expression N Times
Problem
Solution
How It Works
9-14. Repeating a Blank Space N Times
Problem
Solution
How It Works
9-15. Reversing the Order of Characters in a String
Problem
Solution
How It Works
Chapter 10: Working with Dates and Times
10-1. Returning the Current Date and Time
Problem
Solution
How It Works
10-2. Converting Between Time Zones
Problem
Solution
How It Works
10-3. Converting a Date/Time Value to a Datetimeoffset Value
Problem
Solution
How It Works
10-4. Incrementing or Decrementing a Date’s Value
Problem
Solution
How It Works
10-5. Finding the Difference Between Two Dates
Problem
Solution
How It Works
10-6. Finding the Elapsed Time Between Two Dates
Problem
Solution
How It Works
10-7. Displaying the String Value for Part of a Date
Problem
Solution
How It Works
10-8. Displaying the Integer Representations for Parts of a Date
Problem
Solution
How It Works
10-9. Determining Whether a String Is a Valid Date
Problem
Solution
How It Works
10-10. Determining the Last Day of the Month
Problem
Solution
How It Works
10-11. Creating a Date from Numbers
Problem
Solution
How It Works
10-12. Finding the Beginning Date of a Datepart
Problem
Solution #1
Solution #2
Solution #3
How It Works #1
How It Works #2
How It Works #3
10-13. Include Missing Dates
Problem
Solution
How It Works
10-14. Finding Arbitrary Dates
Problem
Solution
How It Works
10-15. Querying for Intervals
Problem
Solution
How It Works
10-16. Working with Dates and Times Across National Boundaries
Problem
Solution
How It Works
Chapter 11: Working with Numbers
11-1. Representing Integers
Problem
Solution
How It Works
11-2. Creating Single-Bit Integers
Problem
Solution
How It Works
11-3. Representing Decimal and Monetary Amounts
Problem
Solution
How It Works
11-4. Representing Floating-Point Values
Problem
Solution
How It Works
11-5. Writing Mathematical Expressions
Problem
Solution
How It Works
11-6. Casting Between Data Types
Problem
Solution
How It Works
11-7. Converting Numbers to Text
Problem
Solution
How It Works
11-8. Converting from Text to a Number
Problem
Solution
How It Works
11-9. Rounding
Problem
Solution
How It Works
11-10. Rounding Always Up or Down
Problem
Solution
How It Works
11-11. Discarding Decimal Places
Problem
Solution
How It Works
11-12. Testing Equality of Binary Floating-Point Values
Problem
Solution
How It Works
11-13. Treating Nulls as Zeros
Problem
Solution
How It Works
11-14. Generating a Row Set of Sequential Numbers
Problem
Solution
How It Works
11-15. Generating Random Integers in a Row Set
Problem
Solution
How It Works
11-16. Reducing Space Used by Decimal Storage
Problem
Solution
How It Works
Chapter 12: Transactions, Locking, Blocking, and Deadlocking
Transaction Control
12-1. Using Explicit Transactions
Problem
Solution
How It Works
12-2. Displaying the Oldest Active Transaction
Problem
Solution
How It Works
12-3. Querying Transaction Information by Session
Problem
Solution
How It Works
Locking
12-4. Viewing Lock Activity
Problem
Solution
How It Works
12-5. Controlling a Table’s Lock-Escalation Behavior
Problem
Solution
How It Works
Transaction, Locking, and Concurrency
12-6. Configuring a Session’s Transaction-Locking Behavior
Problem
Solution
How It Works
Blocking
12-7. Identifying and Resolving Blocking Issues
Problem
Solution
How It Works
12-8. Configuring How Long a Statement Will Wait for a Lock to Be Released
Problem
Solution
How It Works
Deadlocking
12-9. Identifying Deadlocks with a Trace Flag
Problem
Solution
How It Works
12-10. Identifying Deadlocks with Extended Events
Problem
Solution
How It Works
12-11. Setting Deadlock Priority
Problem
Solution
How It Works
Chapter 13: Managing Tables
13-1. Creating a Table
Problem
Solution
How It Works
13-2. Adding a Column
Problem
Solution
How It Works
13-3. Adding a Column that Requires Data
Problem
Solution
How It Works
13-4. Changing a Column
Problem
Solution
How It Works
13-5. Creating a Computed Column
Problem
Solution
How It Works
13-6. Removing a Column
Problem
Solution
How It Works
13-7. Removing a Table
Problem
Solution
How It Works
13-8. Reporting on a Table’s Definition
Problem
Solution
How It Works
13-9. Reducing Storage Used by NULL Columns
Problem
Solution
How It Works
13-10. Adding a Constraint to a Table
Problem
Solution
How It Works
13-11. Creating a Recursive Foreign Key
Problem
Solution
How It Works
13-12. Allowing Data Modifications to Foreign Key Columns in the Referenced Table to Be Reflected in the Referencing Table
Problem
Solution
How It Works
13-13. Specifying Default Values for a Column
Problem
Solution
How It Works
13-14. Validating Data as It Is Entered into a Column
Problem
Solution
How It Works
13-15. Temporarily Turning Off a Constraint
Problem
Solution
How It Works
13-16. Removing a Constraint
Problem
Solution
How It Works
13-17. Creating Auto-incrementing Columns
Problem
Solution
How It Works
13-18. Obtaining the Identity Value Used
Problem
Solution
How It Works
13-19. Viewing or Changing the Seed Settings on an Identity Column
Problem
Solution
How It Works
13-20. Inserting Values into an Identity Column
Problem
Solution
How It Works
13-21. Automatically Inserting Unique Values
Problem
Solution
How It Works
13-22. Using Unique Identifiers Across Multiple Tables
Problem
Solution
How It Works
13-23. Using Temporary Storage
Problem
Solution #1
Solution #2
How It Works
Chapter 14: Managing Views
Regular Views
14-1. Creating a View
Problem
Solution
How It Works
14-2. Querying a View’s Definition
Problem
Solution
How It Works
14-3. Obtaining a List of All Views in a Database
Problem
Solution
How It Works
14-4. Obtaining a List of All Columns in a View
Problem
Solution
How It Works
14-5. Refreshing the Definition of a View
Problem
Solution
How It Works
14-6. Modifying a View
Problem
Solution
How It Works
14-7. Modifying Data Through a View
Problem
Solution
How It Works
14-8. Encrypting a View
Problem
Solution
How It Works
14-9. Indexing a View
Problem
Solution
How It Works
14-10. Creating a Partitioned View
Problem
Solution
How It Works
14-11. Creating a Distributed-Partitioned View
Problem
Solution
How It Works
Chapter 15: Managing Large Tables and Databases
15-1. Partitioning a Table
Problem
Solution
How It Works
15-2. Locating Data in a Partition
Problem
Solution
How It Works
15-3. Adding a Partition
Problem
Solution
How It Works
15-4. Removing a Partition
Problem
Solution
How It Works
15-5. Determining Whether a Table Is Partitioned
Problem
Solution
How It Works
15-6. Determining the Boundary Values for a Partitioned Table
Problem
Solution
How It Works
15-7. Determining the Partitioning Column for a Partitioned Table
Problem
Solution
How It Works
15-8. Determining the NEXT USED Partition
Problem
Solution
How It Works
15-9. Moving a Partition to a Different Partitioned Table
Problem
Solution
How It Works
15-10. Moving Data from a Nonpartitioned Table to a Partition in a Partitioned Table
Problem
Solution
How It Works
15-11. Moving a Partition from a Partitioned Table to a Nonpartitioned Table
Problem
Solution
How It Works
15-12. Reducing Table Locks on Partitioned Tables
Problem
Solution
How It Works
15-13. Removing Partition Functions and Schemes
Problem
Solution
How It Works
15-14. Easing VLDB Manageability (with Filegroups)
Problem
Solution
How It Works
15-15. Compressing Table Data
Problem
Solution
How It Works
15-16. Rebuilding a Heap
Problem
Solution
How It Works
Chapter 16: Managing Indexes
Index Overview
16-1. Creating a Table Index
Problem
Solution
How It Works
16-2. Creating a Table Index
Problem
Solution #1
How It Works
Solution #2
How It Works
16-3. Enforcing Uniqueness on Non-key Columns
Problem
Solution
How It Works
16-4. Creating an Index on Multiple Columns
Problem
Solution
How It Works
16-5. Defining Index Column Sort Direction
Problem
Solution
How It Works
16-6. Viewing Index Metadata
Problem
Solution
How It Works
16-7. Disabling an Index
Problem
Solution
How It Works
16-8. Dropping Indexes
Problem
Solution
How It Works
16-9. Changing an Existing Index
Problem
Solution
How It Works
Controlling Index Build Performance and Concurrency
16-10. Sorting in Tempdb
Problem
Solution
How It Works
16-11. Controlling Index Creation Parallelism
Problem
Solution
How It Works
16-12. User Table Access During Index Creation
Problem
Solution
How It Works
Index Options
16-13. Using an Index INCLUDE
Problem
Solution
How It Works
16-14. Using PADINDEX and FILLFACTOR
Problem
Solution
How It Works
16-15. Disabling Page and/or Row Index Locking
Problem
Solution
How It Works
Managing Very Large Indexes
16-16. Creating an Index on a Filegroup
Problem
Solution
How It Works
16-17. Implementing Index Partitioning
Problem
Solution
How It Works
16-18. Indexing a Subset of Rows
Problem
Solution
How It Works
16-19. Reducing Index Size
Problem
Solution
How It Works
16-20. Further Reducing Index Size
Problem
Solution
How It Works
Chapter 17: Stored Procedures
17-1. Creating a Stored Procedure
Problem
Solution
How It Works
17-2. Passing Parameters
Problem
Solution
How It Works
17-3. Making Parameters Optional
Problem
Solution
How It Works
17-4. Making Early Parameters Optional
Problem
Solution
How It Works
17-5. Returning Output
Problem
Solution
How It Works
17-6. Modifying a Stored Procedure
Problem
Solution
How It Works
17-7. Removing a Stored Procedure
Problem
Solution
How It Works
17-8. Automatically Run a Stored Procedure at Start-Up
Problem
Solution
How It Works
17-9. Viewing a Stored Procedure’s Definition
Problem
Solution
How It Works
17-10. Documenting Stored Procedures
Problem
Solution
How It Works
17-11. Determining the Current Nesting Level
Problem
Solution
How It Works
17-12. Encrypting a Stored Procedure
Problem
Solution
How It Works
17-13. Specifying a Security Context
Problem
Solution
How It Works
17-14. Avoiding Cached Query Plans
Problem
Solution
How It Works
17-15. Flushing the Procedure Cache
Problem
Solution
How It Works
Chapter 18: User-Defined Functions and Types
UDF Basics
18-1. Creating Scalar Functions
Problem
Solution
How It Works
18-2. Creating Inline Functions
Problem
Solution
How It Works
18-3. Creating Multi-Statement User-Defined Functions
Problem
Solution
How It Works
18-4. Modifying User-Defined Functions
Problem
Solution
How It Works
18-5. Viewing UDF Metadata
Problem
Solution
How It Works
Benefitting from UDFs
18-6. Maintaining Reusable Code
Problem
Solution
How It Works
18-7. Cross-Referencing Natural Key Values
Problem
Solution
How It Works
18-8. Replacing a View with a Function
Problem
Solution
How It Works
18-9. Dropping a Function
Problem
Solution
How It Works
UDT Basics
18-10. Creating and Using User-Defined Types
Problem
Solution
How It Works
18-11. Identifying Dependencies on User-Defined Types
Problem
Solution
How It Works
18-12. Passing Table-Valued Parameters
Problem
Solution
How It Works
18-13. Dropping User-Defined Types
Problem
Solution
How It Works
Chapter 19: In-Memory OLTP
19-1. Configuring a Database So That It Can Utilize In-Memory OLTP
Problem
Solution #1
Solution #2
How It Works
19-2. Making a Memory-Optimized Table
Problem
Solution
How It Works
19-3. Creating a Memory-Optimized Table Variable
Problem
Solution
How It Works
19-4. Creating a Natively Compiled Stored Procedure
Problem
Solution
How It Works
19-5. Determining Which Database Objects Are Configured to Use In-Memory OLTP
Problem
Solution
How It Works
19-6. Determining Which Objects Are Actively Using In-Memory OLTP on the Server
Problem
Solution
How It Works
19-7. Detecting Performance Issues with Natively Compiled Stored Procedure Parameters
Problem
Solution
How It Works
19-8. Viewing CFP Metadata
Problem
Solution
How It Works
19-9. Disabling or Enabling Automatic Merging
Problem
Solution
How It Works
19-10. Manually Merging Checkpoint File Pairs
Problem
Solution
How It Works
Chapter 20: Triggers
20-1. Creating an AFTER DML Trigger
Problem
Solution
How It Works
20-2. Creating an INSTEAD OF DML Trigger
Problem
Solution
How It Works
20-3. Handling Transactions in Triggers
Problem
Solution
How It Works
20-4. Linking Trigger Execution to Modified Columns
Problem
Solution
How It Works
20-5. Viewing DML Trigger Metadata
Problem
Solution
How It Works
20-6. Creating a DDL Trigger
Problem
Solution
How It Works
20-7. Creating a Logon Trigger
Problem
Solution
How It Works
20-8. Viewing DDL Trigger Metadata
Problem
Solution
How It Works
20-9. Modifying a Trigger
Problem
Solution
How It Works
20-10. Enabling and Disabling a Trigger
Problem
Solution
How It Works
20-11. Nesting Triggers
Problem
Solution
How It Works
20-12. Controlling Recursion
Problem
Solution
How It Works
20-13. Specifying the Firing Order
Problem
Solution
How It Works
20-14. Dropping a Trigger
Problem
Solution
How It Works
Chapter 21: Error Handling
21-1. Handling Batch Errors
Problem
Solution
How It Works
21-2. What Are the Error Numbers and Messages Within SQL?
Problem
Solution
How It Works
21-3. How Can I Implement Structured Error Handling in My Queries?
Problem
Solution
How It Works
21-4. How Can I Use Structured Error Handling, but Still Return an Error?
Problem
Solution
How It Works
21-5. Nested Error Handling
Problem
Solution
How It Works
21-6. Throwing an Error
Problem
Solution #1: Use RAISERROR to throw an error
How It Works
Solution #2: Use THROW to throw an error
How It Works
21-7. Creating a User-Defined Error
Problem
Solution
How It Works
21-8. Removing a User-Defined Error
Problem
Solution
How It Works
Chapter 22: Query Performance Tuning
Query Performance Tips
Capturing and Evaluating Query Performance
22-1. Capturing Executing Queries
Problem
Solution #1
How It Works
Solution #2
How It Works
22-2. Viewing Estimated Query Execution Plans
Problem
Solution
How It Works
22-3. Viewing Execution Runtime Information
Problem
Solution
How It Works
22-4. Viewing Statistics for Cached Plans
Problem
Solution
How It Works
22-5. Viewing Record Counts for Cached Plans
Problem
Solution
How It Works
22-6. Viewing Aggregated Performance Statistics Based on Query or Plan Patterns
Problem
Solution
How It Works
22-7. Identifying the Top Bottleneck
Problem
Solution
How It Works
22-8. Identifying I/O Contention by Database and File
Problem
Solution
How It Works
Miscellaneous Techniques
22-9. Parameterizing Ad Hoc Queries
Problem
Solution
How It Works
22-10. Forcing the Use of a Query Plan
Problem
Solution
How It Works
22-11. Applying Hints Without Modifying a SQL Statement
Problem
Solution
How It Works
22-12. Creating Plan Guides from Cache
Problem
Solution
How It Works
22-13. Checking the Validity of a Plan Guide
Problem
Solution
How It Works
22-14. Parameterizing a Nonparameterized Query Using Plan Guides
Problem
Solution
How It Works
22-15. Limiting Competing Query Resource Consumption
Problem
Solution
How It Works
Chapter 23: Hints
23-1. Forcing a Join’s Execution Approach
Problem
Solution
How It Works
23-2. Forcing a Statement Recompile
Problem
Solution
How It Works
23-3. Executing a Query Without Locking
Problem
Solution #1: The NOLOCK Hint
Solution #2: The Isolation Level
How It Works
23-4. Forcing an Index Seek
Problem
Solution
How It Works
23-5. Forcing an Index Scan
Problem
Solution
How It Works
23-6. Optimizing for First Rows
Problem
Solution
How It Works
23-7. Specifying Join Order
Problem
Solution
How It Works
23-8. Forcing the Use of a Specific Index
Problem
Solution
How It Works
23-9. Optimizing for Specific Parameter Values
Problem
Solution
How It Works
Chapter 24: Index Tuning and Statistics
Index Tuning
Index Maintenance
24-1. Displaying Index Fragmentation
Problem
Solution
How It Works
24-2. Rebuilding Indexes
Problem
Solution
How It Works
24-3. Defragmenting Indexes
Problem
Solution
How It Works
24-4. Rebuilding a Heap
Problem
Solution
How It Works
24-5. Displaying Index Usage
Problem
Solution
How It Works
Statistics
24-6. Manually Creating Statistics
Problem
Solution
How It Works
24-7. Creating Statistics on a Subset of Rows
Problem
Solution
How It Works
24-8. Updating Statistics
Problem
Solution
How It Works
24-9. Generating Statistics Across All Tables
Problem
Solution
How It Works
24-10. Updating Statistics Across All Tables
Problem
Solution
How It Works
24-11. Viewing Statistics Details
Problem
Solution
How It Works
24-12. Removing Statistics
Problem
Solution
How It Works
24-13. Finding When Stats Need to Be Created
Problem
Solution
How It Works
Chapter 25: XML
25-1. Creating an XML Column
Problem
Solution
How It Works
25-2. Inserting XML Data
Problem
Solution
How It Works
25-3. Validating XML Data
Problem
Solution
How It Works
25-4. Verifying the Existence of XML Schema Collections
Problem
Solution
How It Works
25-5. Retrieving XML Data
Problem
Solution
How It Works
25-6. Modifying XML Data
Problem
Solution
How It Works
25-7. Indexing XML Data
Problem
Solution
How It Works
25-8. Formatting Relational Data as XML
Problem
Solution
How It Works
25-9. Formatting XML Data as Relational
Problem
Solution
How It Works
25-10. Using XML to Return a Delimited String
Problem
Solution
How It Works
Chapter 26: Files, Filegroups, and Integrity
26-1. Adding a Data File or a Log File
Problem
Solution
How It Works
26-2. Retrieving Information about the Files in a Database
Problem
Solution
How It Works
26-3. Removing a Data File or a Log File
Problem
Solution
How It Works
26-4. Relocating a Data File or a Log File
Problem
Solution
How It Works
26-5. Changing a File’s Logical Name
Problem
Solution
How It Works
26-6. Increasing the Size of a Database File
Problem
Solution
How It Works
26-7. Adding a Filegroup
Problem
Solution
How It Works
26-8. Adding a File to a Filegroup
Problem
Solution
How It Works
26-9. Setting the Default Filegroup
Problem
Solution
How It Works
26-10. Adding Data to a Specific Filegroup
Problem
Solution
How It Works
26-11. Moving Data to a Different Filegroup
Problem
Solution #1
Solution #2
Solution #3
How It Works
26-12. Removing a Filegroup
Problem
Solution
How It Works
26-13. Making a Database or a Filegroup Read-Only
Problem #1
Problem #2
Solution #1
Solution #2
How It Works
26-14. Viewing Database Space Usage
Problem
Solution #1
Solution #2
Solution #3
Solution #4
How It Works
26-15. Shrinking the Database or a Database File
Problem
Solution #1
Solution #2
How It Works
26-16. Checking the Consistency of Allocation Structures
Problem
Solution
How It Works
26-17. Checking Allocation and Structural Integrity
Problem
Solution
How It Works
26-18. Checking the Integrity of Tables in a Filegroup
Problem
Solution
How It Works
26-19. Checking the Integrity of Specific Tables and Indexed Views
Problem
Solution #1
Solution #2
Solution #3
How It Works
26-20. Checking Constraint Integrity
Problem
Solution
How It Works
26-21. Checking System Table Consistency
Problem
Solution
How It Works
Chapter 27: Backup
27-1. Backing Up a Database
Problem
Solution
How It Works
27-2. Compressing a Backup
Problem
Solution
How It Works
27-3. Ensuring That a Backup Can Be Restored
Problem
Solution
How It Works
27-4. Transaction Log Backup
Problem
Solution
How It Works
27-5. Understanding Why the Transaction Log Continues to Grow
Problem
Solution
How It Works
27-6. Performing a Differential Backup
Problem
Solution
How It Works
27-7. Backing Up a Single Row or Table
Problem
Solution
How It Works
27-8. Creating a Database Snapshot
Problem
Solution
How It Works
27-9. Backing Up Data Files or Filegroups
Problem
Solution #1: Perform a File Backup
Solution #2: Perform a Filegroup Backup
How It Works
27-10. Mirroring Backup Files
Problem
Solution
How It Works
27-11. Backing Up a Database Without Affecting the Normal Sequence of Backups
Problem
Solution
How It Works
27-12. Querying Backup Data
Problem
Solution
How It Works
27-13. Encrypting a Backup
Problem
Solution
How It Works
27-14. Compressing an Encrypted Backup
Problem
Solution
How It Works
27-15. Backing Up Certificates
Problem
Solution
How It Works
27-16. Backing Up to Azure
Problem
Solution
How It Works
27-17. Backing Up to Multiple Files
Problem
Solution
How It Works
Chapter 28: Recovery
28-1. Restoring a Database from a Full Backup
Problem
Solution
How It Works
28-2. Restoring a Database from a Transaction Log Backup
Problem
Solution
How It Works
28-3. Restoring a Database from a Differential Backup
Problem
Solution
How It Works
28-4. Restoring a File or Filegroup
Problem
Solution
How It Works
28-5. Performing a Piecemeal (PARTIAL) Restore
Problem
Solution
How It Works
28-6. Restoring a Page
Problem
Solution
How It Works
28-7. Identifying Databases with Multiple Recovery Paths
Problem
Solution
How It Works
28-8. Restore a Single Row or Table
Problem
Solution #1: Restore Rows from a Backup
How It Works
Solution #2: Restore Rows from a Database Snapshot
How It Works
28-9. Recover from a Backup in Azure Blob Storage
Problem
Solution
How It Works
28-10. Recover a Certificate
Problem
Solution
How It Works
Chapter 29: Principals and Users
Windows Principals
29-1. Creating a Windows Login
Problem
Solution
How It Works
29-2. Viewing Windows Logins
Problem
Solution
How It Works
29-3. Altering a Windows Login
Problem
Solution
How It Works
29-4. Dropping a Windows Login
Problem
Solution
How It Works
29-5. Denying SQL Server Access to a Windows User or Group
Problem
Solution
How It Works
SQL Server Principals
29-6. Creating a SQL Server Login
Problem
Solution
How It Works
29-7. Viewing SQL Server Logins
Problem
Solution
How It Works
29-8. Altering a SQL Server Login
Problem
Solution
How It Works
29-9. Managing a Login’s Password
Problem
Solution
How It Works
29-10. Dropping a SQL Login
Problem
Solution
How It Works
29-11. Managing Server Role Members
Problem
Solution
How It Works
29-12. Reporting Fixed Server Role Information
Problem
Solution
How It Works
Database Principals
29-13. Creating Database Users
Problem
Solution
How It Works
29-14. Reporting Database User Information
Problem
Solution
How It Works
29-15. Modifying a Database User
Problem
Solution
How It Works
29-16. Removing a Database User from the Database
Problem
Solution
How It Works
29-17. Fixing Orphaned Database Users
Problem
Solution
How It Works
29-18. Reporting Fixed Database Roles Information
Problem
Solution
How It Works
29-19. Managing Fixed Database Role Membership
Problem
Solution
How It Works
29-20. Managing User-Defined Database Roles
Problem
Solution
How It Works
29-21. Managing Application Roles
Problem
Solution
How It Works
29-22. Managing User-Defined Server Roles
Problem
Solution
How It Works
Chapter 30: Securables, Permissions, and Auditing
Permissions Overview
30-1. Reporting SQL Server Assignable Permissions
Problem
Solution
How It Works
Server-Scoped Securables and Permissions
30-2. Managing Server Permissions
Problem
Solution
How It Works
30-3. Querying Server-Level Permissions
Problem
Solution
How It Works
Database-Scoped Securables and Permissions
30-4. Managing Database Permissions
Problem
Solution
How It Works
30-5. Querying Database Permissions
Problem
Solution
How It Works
Schema-Scoped Securables and Permissions
30-6. Managing Schemas
Problem
Solution
How It Works
30-7. Managing Schema Permissions
Problem
Solution
How It Works
Object Permissions
30-8. Managing Object Permissions
Problem
Solution
How It Works
Managing Permissions Across Securable Scopes
30-9. Determining Permissions to a Securable
Problem
Solution
How It Works
30-10. Reporting Permissions by Securable Scope
Problem
Solution
How It Works
30-11. Changing Securable Ownership
Problem
Solution
How It Works
30-12. Allowing Access to Non-SQL Server Resources
Problem
Solution
How It Works
Auditing Activity of Principals Against Securables
30-13. Defining Audit Data Sources
Problem
Solution
How It Works
30-14. Capturing SQL Instance–Scoped Events
Problem
Solution
How It Works
30-15. Capturing Database-Scoped Events
Problem
Solution
How It Works
30-16. Querying Captured Audit Data
Problem
Solution
How It Works
30-17. Managing, Modifying, and Removing Audit Objects
Problem
Solution
How It Works
Chapter 31: Objects and Dependencies
31-1. Changing the Name of Database Items
Problem
Solution
How It Works
31-2. Changing an Object’s Schema
Problem
Solution
How It Works
31-3. Identifying Object Dependencies
Problem
Solution
How It Works
31-4. Identifying Referencing and Referenced Entities
Problem
Solution
How It Works
31-5. Viewing the Definition of Coded Objects
Problem
Solution #1
Solution #2
How It Works
31-6. Returning a Database Object’s Name, Schema Name, and Object ID
Problem
Solution #1
Solution #2
How It Works
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset