Contents
Chapter 1: Getting Started with SELECT
1-2. Retrieving Specific Columns
1-4. Specifying the Rows to Be Returned
1-5. Renaming the Output Columns
1-6. Building a Column from an Expression
1-7. Providing Shorthand Names for Tables
1-8. Negating a Search Condition
1-9. Specifying A Range of Values
1-10. Checking for NULL Values
1-11. Providing a List of Values
1-12. Performing Wildcard Searches
1-15. Sorting by Columns Not Selected
1-16. Forcing Unusual Sort Orders
1-17. Paging Through A Result Set
Chapter 2: Elementary Programming
2-2. Retrieving a Value into a Variable
2-3. Writing an IF…THEN…ELSE Statement
2-4. Writing a Simple CASE Expression
2-5. Writing a Searched CASE Expression
2-6. Writing a WHILE Statement
2-7. Returning from the Current Execution Scope
Solution #1: Exit with No Return Value
Solution #2: Exit and Provide a Value
2-8. Going to a Label in a Transact-SQL Batch
2-9. Pausing Execution for a Period of Time
2-10. Creating and Using Cursors
Chapter 3: NULLs and Other Pitfalls
3-1. Replacing NULL with an Alternate Value
3-2. Returning the First Non-NULL Value from a List
3-3. Choosing Between ISNULL and COALESCE in a SELECT Statement
3-4. Looking for NULLs in a Table
3-5. Removing Values from an Aggregate
3-6. Enforcing Uniqueness with NULL Values
3-7. Enforcing Referential Integrity on Nullable Columns
3-8. Joining Tables on Nullable Columns
Chapter4: Querying from Multiple Tables
4-1. Correlating Parent and Child Rows
4-2. Querying Many-to-Many Relationships
4-3. Making One Side of a Join Optional
4-4. Making Both Sides of a Join Optional
4-5. Generating All Possible Row Combinations
4-6. Selecting from a Result Set
4-7. Testing for the Existence of a Row
4-8. Testing Against the Result from a Query
4-9. Comparing Subsets of a Table
4-10. Stacking Two Row Sets Vertically
4-11. Eliminating Duplicate Values from a Union
4-12. Subtracting One Row Set from Another
4-13. Finding Rows in Common Between Two Row Sets
4-14. Finding Rows That Are Missing
Chapter 5: Grouping and Summarizing
5-3. Restricting a Result Set to Groups of Interest
5-4. Removing Duplicates from the Detailed Results
5-6. Creating Hierarchical Summaries
5-7. Creating Custom Summaries
5-8. Identifying Rows Generated by the GROUP BY Arguments
5-9. Identifying Summary Levels
Chapter 6: Advanced Select Techniques
6-1. Avoiding Duplicate Results
6-3. Renaming a Column in the Output
6-4. Retrieving Data Directly into Variables
6-5. Creating a New Table with the Results from a Query
6-6. Filtering on the Results from a Subquery
6-7. Selecting from the Results of Another Query
6-8. Passing Rows Through a Function
6-9. Returning Random Rows from a Table
6-10. Converting Rows into Columns
6-11. Converting Columns into Rows
6-12. Reusing Common Subqueries in a Query
6-13. Querying Recursive Tables
6-14. Hard-Coding the Results from a Query
Chapter 7: Aggregations and Windowing
7-2. Counting the Rows in a Group
7-3. Summing the Values in a Group
7-4. Finding the High and Low Values in a Group
7-5. Detecting Changes in a Table
7-6. Finding the Statistical Variance in the Values of a Column
7-7. Finding the Standard Deviation in the Values of a Column
7-8. Calculating Totals Based Upon the Prior Row
7-9. Calculating Totals Based Upon a Subset of Rows
7-11. Generating an Incrementing Row Number
7-13. Returning Rows by Rank Without Gaps
7-14. Sorting Rows into Buckets
7-15. Grouping Logically Consec44utive Rows Together
7-16. Accessing Values from Other Rows
7-17. Accessing the First or Last Value from a Partition
7-18. Calculating the Relative Position or Rank of a Value in a Set of Values
7-19. Calculating Continuous or Discrete Percentiles
7-20. Assigning Sequences in a Specified Order
Chapter 8: Inserting, Updating, Deleting
8-2. Specifying Default Values
8-3. Overriding an IDENTITY Column
8-4. Generating a Globally Unique Identifier (GUID)
8-5. Inserting Results from a Query
8-6. Inserting Results from a Stored Procedure
8-7. Inserting Multiple Rows at Once
8-8. Inserting Rows and Returning the Inserted Rows
8-9. Updating a Single Row or Set of Rows
8-10. Updating with a Sec29ond Table as the Data Source
8-11. Updating Data and Returning the Affected Rows
8-12. Updating Large-Value Columns
8-14. Deleting Rows and Returning the Deleted Rows
8-15. Deleting All Rows Quickly (Truncating)
8-16. Merging Data (Inserting, Updating, or Deleting Values)
Chapter 9: Working with Strings
9-1. Concatenating Multiple Strings
9-2. Finding a Character’s ASCII Value
9-3. Returning Integer and Character Unicode Values
9-5. Determining the Similarity of Strings
9-6. Returning the Left-Most Portion of a String
9-7. Returning Part of a String
9-8. Counting Characters or Bytes in a String
9-9. Replacing Part of a String
9-10. Stuffing a String into a String
9-11. Changing Between Lower- and Uppercase
9-12. Removing Leading and Trailing Blanks
9-13. Repeating an Expression N Times
9-14. Repeating a Blank Space N Times
9-15. Reversing the order of Characters in a String
Chapter 10: Working with Dates and Times
10-1. Returning the Current Date and Time
10-2. Converting Between Time Zones
10-3. Converting a Date/Time Value to a Datetimeoffset Value
10-4. Incrementing or Decrementing a Date’s Value
10-5. Finding the Difference Between Two Dates
10-6. Finding the Elapsed Time Between Two Dates
10-7. Displaying the String Value for Part of a Date
10-8. Displaying the Integer Representations for Parts of a Date
10-9. Determining Whether a String Is a Valid Date
10-10. Determining the Last Day of the Month
10-11. Creating a Date from Numbers
10-12. Finding the Beginning Date of a Datepart
10-14. Finding Arbitrary Dates
10-16. Working with Dates and Times Across National Boundaries
Chapter 11: Working with Numbers
11-2. Representing Decimal Amounts
11-3. Representing Monetary Amounts
11-4. Representing Floating-Point Values
11-5. Writing Mathematical Expressions
11-6. Guarding Against Errors in Expressions with Mixed Data Types
11-8. Rounding Always Up or Down
11-9. Discarding Decimal Places
11-10. Testing Equality of Binary Floating-Point Values
11-11. Treating Nulls as Zeros
11-12. Generating a Row Set of Sequential Numbers
11-13. Generating Random Integers in a Row Set
11-14. Reducing Space Used by Decimal Storage
Chapter 12: Transactions, Locking, Blocking, and Deadlocking
12-1. Using Explicit Transactions
12-2. Displaying the Oldest Active Transaction
12-3. Querying Transaction Information by Session
12-5. Controlling a Table’s Lock Escalation Behavior
Transaction, Locking, and Concurrency
12-6. Configuring a Session’s Transaction Locking Behavior
12-7. Identifying and Resolving Blocking Issues
12-8. Configuring How Long a Statement Will Wait for a Lock to Be Released
12-9. Identifying Deadlocks with a Trace Flag
12-10. Setting Deadlock Priority
13-3. Adding a Column That Requires Data
13-5. Creating a Computed Column
13-8. Reporting on a Table’s Definition
13-9. Reducing Storage Used by NULL Columns
13-10. Adding a Constraint to a Table
13-11. Creating a Recursive Foreign Key
13-13. Specifying Default Values for a Column
13-14. Validating Data as It Is Entered into a Column
13-15. Temporarily Turning Off a Constraint
13-17. Creating Auto-incrementing Columns
13-18. Obtaining the Identity Value Used
13-19. Viewing or Changing the Seed Settings on an Identity Column
13-20. Inserting Values into an Identity Column
13-21. Automatically Inserting Unique Values
13-22. Using Unique Identifiers Across Multiple Tables
13-23. Using Temporary Storage
14-2. Querying a View’s Definition
14-3. Obtaining a List of All Views in a Database
14-4. Obtaining a List of All Columns in a View
14-5. Refreshing the Definition of a View
14-7. Modifying Data Through a View
14-10. Creating a Partitioned View
14-11. Creating a Distributed Partitioned View
Chapter 15: Managing Large Tables and Databases
15-2. Locating Data in a Partition
15-5. Determining Whether a Table Is Partitioned
15-6. Determining the Boundary Values for a Partitioned Table
15-7. Determining the Partitioning Column for a Partitioned Table
15-8. Moving a Partition to a Different Partitioned Table
15-9. Moving Data from a Nonpartitioned Table to a Partition in a Partitioned Table
15-10. Moving a Partition from a Partitioned Table to a Nonpartitioned Table
15-11. Reducing Table Locks on Partitioned Tables
15-12. Removing Partition Functions and Schemes
15-13. Easing VLDB Manageability (with Filegroups)
16-2. Enforcing Uniqueness on Non-key Columns
16-3. Creating an Index on Multiple Columns
16-4. Defining Index Column Sort Direction
16-8. Changing an Existing Index
Controlling Index Build Performance and Concurrency
16-10. Controlling Index Creation Parallelism
16-11. User Table Access During Index Creation
16-13. Using PADINDEX and FILLFACTOR
16-14. Disabling Page and/or Row Index Locking
16-15. Creating an Index on a Filegroup
16-16. Implementing Index Partitioning
16-17. Indexing a Subset of Rows
17-2. Creating a Stored Procedure
17-3. Generalizing a Stored Procedure
17-4. Making Parameters Optional
17-5. Making Early Parameters Optional
17-7. Modifying a Stored Procedure
17-8. Removing a Stored Procedure
17-9. Automatically Run a Stored Procedure at Start-Up
17-10. Viewing a Stored Procedure’s Definition
17-11. Documenting Stored Procedures
17-12. Determining the Current Nesting Level
17-13. Encrypting a Stored Procedure
17-14. Specifying a Security Context
17-15. Avoiding Cached Query Plans
17-16. Flushing the Procedure Cache
Chapter 18: User-Defined Functions and Types
18-1. Creating Scalar Functions
18-2. Creating Inline Functions
18-3. Creating Multi-Statement User-Defined Functions
18-4. Modifying User-Defined Functions
18-6. Maintaining Reusable Code
18-7. Cross-Referencing Natural Key Values
18-8. Replacing a View with a Function
18-10. Creating and Using User-Defined Types
18-11. Identifying Dependencies on User-Defined Types
18-12. Passing Table-Valued Parameters
18-13. Dropping User-Defined Types
19-1. Creating an AFTER DML Trigger
19-2. Creating an INSTEAD OF DML Trigger
19-3. Handling Transactions in Triggers
19-4. Linking Trigger Execution to Modified Columns
19-5. Viewing DML Trigger Metadata
19-7. Creating a Logon Trigger
19-8. Viewing DDL Trigger Metadata
19-10. Enabling and Disabling a Trigger
19-13. Specifying the Firing Order
20-2. What are the error numbers and messages within SQL?
20-3. How can I implement structured error handling in my queries?
20-4. How can I use structured error handling, but still return an error?
Solution #1: Use RAISERROR to throw an error
Solution #2: Use THROW to throw an error
20-7. Creating a user defined error
Solution: Use sp_addmessage to create user defined error message
20-7. Removing a user defined error
Solution: Use sp_dropmessage to remove the user defined error message
Chapter 21: Query Performance Tuning
Capturing and Evaluating Query Performance
21-1. Capturing Executing Queries
21-2. Viewing Estimated Query Execution Plans
21-3. Viewing Execution Runtime Information
21-4. Viewing Statistics for Cached Plans
21-5. Viewing Record Counts for Cached Plans
21-6. Viewing Aggregated Performance Statistics Based on Query or Plan Patterns
21-7. Identifying the Top Bottleneck
21-8. Identifying I/O Contention by Database and File
21-9. Parameterizing Ad Hoc Queries
21-10. Forcing Use of a Query Plan
21-11. Applying Hints Without Modifying a SQL Statement
21-12. Creating Plan Guides from Cache
21-13. Checking the Validity of a Plan Guide
21-14. Parameterizing a Nonparameterized Query Using Plan Guides
21-15. Limiting Competing Query Resource Consumption
22-1. Forcing a Join’s Execution Approach
22-2. Forcing a Statement Recompile
22-3. Executing a Query Without Locking
Solution #2: The Isolation Level
22-6. Optimizing for First Rows
22-8. Forcing Use of a Specific Index
22-9. Optimizing for Specific Parameter Values
Chapter 23: Index Tuning and Statistics
23-1. Displaying Index Fragmentation
23-6. Manually Creating Statistics
23-7. Creating Statistics on a Subset of Rows
23-9. Generating Statistics Across All Tables
23-10. Updating Statistics Across All Tables
23-11. Viewing Statistics Details
24-4. Verifying the Existence of XML Schema Collections
24-8. Formatting Relational Data as XML
24-9. Formatting XML Data as Relational
Chapter 25: Files, Filegroups, and Integrity
25-1. Adding a Data File or a Log File
25-2. Removing a Data File or a Log File
25-3. Relocating a Data File or a Log File
25-4. Changing a File’s Logical Name
25-5. Increasing the Size of a Database File
25-7. Adding a File to a Filegroup
25-8. Setting the Default Filegroup
25-9. Adding Data to a Specific Filegroup
25-10. Moving Data to a Different Filegroup
25-12. Making a Database or a Filegroup Read-Only
25-13. Viewing Database Space Usage
25-14. Shrinking the Database or a Database File
25.15. Checking Consistency of Allocation Structures
25-16. Checking Allocation and Structural Integrity
25-17. Checking Integrity of Tables in a Filegroup
25-18. Checking Integrity of Specific Tables and Indexed Views
25-19. Checking Constraint Integrity
25-20. Checking System Table Consistency
26-3. Ensuring That a Backup Can Be Restored
26-4. Understanding Why the Transaction Log Continues to Grow
26-5. Performing a Differential Backup
26-6. Backing Up a Single Row or Table
Solution #1: Restore Rows from a Backup
Solution #2: Restore Rows from a Database Snapshot
26-7. Backing Up Data Files or Filegroups
Solution #1: Perform a File Backup
Solution #2: Perform a Filegroup Backup
26-9. Backing Up a Database Without Affecting the Normal Sequence of Backups
27-1. Restoring a Database from a Full Backup
27-2. Restoring a Database from a Transaction Log Backup
27-3. Restoring a Database from a Differential Backup
27-4. Restoring a File or Filegroup
27-5. Performing a Piecemeal (PARTIAL) Restore
27-7. Identifying Databases with Multiple Recovery Paths
Chapter 28: Principals and Users
28-1. Creating a Windows Login
28-3. Altering a Windows Login
28-4. Dropping a Windows Login
28-5. Denying SQL Server Access to a Windows User or Group
28-6. Creating a SQL Server Login
28-7. Viewing SQL Server Logins
28-8. Altering a SQL Server Login
28-9. Managing a Login’s Password
28-11. Managing Server Role Members
28-12. Reporting Fixed Server Role Information
28-13. Creating Database Users
28-14. Reporting Database User Information
28-15. Modifying a Database User
28-16. Removing a Database User from the Database
28-17. Fixing Orphaned Database Users
28-18. Reporting Fixed Database Roles Information
28-19. Managing Fixed Database Role Membership
28-20. Managing User-Defined Database Roles
28-21. Managing Application Roles
Chapter 29: Securables, Permissions, and Auditing
29-1. Reporting SQL Server Assignable Permissions
Server-Scoped Securables and Permissions
29-2. Managing Server Permissions
29-3. Querying Server-Level Permissions
Database-Scoped Sec14urables and Permissions
29-4. Managing Database Permissions
29-5. Querying Database Permissions
Schema-Scoped Securables and Permissions
29-7. Managing Schema Permissions
29-8. Managing Object Permissions
Managing Permissions Across Securable Scopes
29-9. Determining Permissions to a Securable
29-10. Reporting Permissions by Securable Scope
29-11. Changing Securable Ownership
29-12. Allowing Access to Non-SQL Server Resources
Auditing Activity of Principals Against Securables
29-13. Defining Audit Data Sources
29-14. Capturing SQL Instance–Scoped Events
29-15. Capturing Database-Scoped Events
29-16. Querying Captured Audit Data
29-17. Managing, Modifying, and Removing Audit Objects
Chapter 30: Objects and Dependencies
30-1. Changing the Name of a Database Object
30-2. Changing an Object’s Schema
30-3. Identifying Object Dependencies
30-4. Identifying Referencing and Referenced Entities
30-5. Viewing an Object’s Definition
30-6. Returning a Database Object’s Name, Schema Name, and Object ID