Dedication
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
- 1-2. Checking the Database Server Version
- 1-3. Checking the Database Name
- 1-4. Checking Your Username
- 1-5. Querying a Table
- 1-6. Returning Specific Rows
- 1-7. Listing the Available Tables
- 1-8. Naming the Output Columns
- 1-9. Providing Shorthand Names for Tables
- 1-10. Computing New Columns from Existing Data
- 1-11. Negating a Search Condition
- 1-12. Keeping the WHERE Clause Unambiguous
- 1-13. Testing for Existence
- 1-14. Specifying a Range of Values
- 1-15. Checking for Null Values
- 1-16. Writing an IN-List
- 1-17. Performing Wildcard Searches
- 1-18. Sorting Your Results
- 1-19. Specifying the Case-Sensitivity of a Sort
- 1-20. Sorting Nulls High or Low
- 1-21. Forcing Unusual Sort Orders
- 1-22. Paging Through a Result Set
- 1-23. Sampling a Subset of Rows
- Chapter 2: Elementary Programming
- 2-1. Executing T-SQL from a File
- 2-2. Retrieving Values into Variables
- 2-3. Writing Expressions
- 2-4. Deciding Between Two Execution Paths
- 2-5. Detecting Whether Rows Exist
- 2-6. Going to a Label in a Transact-SQL Batch
- 2-7. Trapping and Throwing Errors
- 2-8. Returning from the Current Execution Scope
- 2-9. Writing a Simple CASE Expression
- 2-10. Writing a Searched CASE Expression
- 2-11. Repeatedly Executing a Section of Code
- 2-12. Controlling Iteration in a Loop
- 2-13. Pausing Execution for a Period of Time
- 2-14. Looping through Query Results a Row at a Time
- Chapter 3: Working with NULLS
- 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
- Chapter 4: 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. Introducing New Columns
- 4-8. Testing for the Existence of a Row
- 4-9. Testing Against the Result from a Query
- 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
- 4-15. Comparing Two Tables
- Chapter 5: Aggregations and Grouping
- 5-1. Computing an Aggregation
- 5-2. Creating Aggregations Based upon the Values of the Data
- 5-3. Counting the Rows in a Group
- 5-4. Detecting Changes in a Table
- 5-5. Restricting a Result Set to Groups of Interest
- 5-6. Performing Aggregations against Unique Values Only
- 5-7. Creating Hierarchical Summaries
- 5-8. Creating Summary Totals and Subtotals
- 5-9. Creating Custom Summaries
- 5-10. Identifying Rows Generated by the GROUP BY Arguments
- 5-11. Identifying Summary Levels
- Chapter 6: Advanced Select Techniques
- 6-1. Avoiding Duplicate Results
- 6-2. Returning the Top N Rows
- 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 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: Windowing Functions
- 7-1. Calculating Totals Based upon the Prior Row
- 7-2. Calculating Totals Based upon a Subset of Rows
- 7-3. Calculating a Percentage of Total
- 7-4. Calculating a “Row X of Y”
- 7-5. Using a Logical Window
- 7-6. Generating an Incrementing Row Number
- 7-7. Returning Rows by Rank
- 7-8. Sorting Rows into Buckets
- 7-9. Grouping Logically Consecutive Rows Together
- 7-10. Accessing Values from Other Rows
- 7-11. Finding Gaps in a Sequence of Numbers
- 7-12. Accessing the First or Last Value from a Partition
- 7-13. Calculating the Relative Position or Rank of a Value within a Set of Values
- 7-14. Calculating Continuous or Discrete Percentiles
- 7-15. Assigning Sequences in a Specified Order
- Chapter 8: Inserting, Updating, Deleting
- 8-1. Inserting a New Row
- 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 from Supplied Values
- 8-8. Inserting Rows and Returning the Inserted Rows
- 8-9. Updating a Single Row or Set of Rows
- 8-10. Updating While Using a Second Table as the Data Source
- 8-11. Updating Data and Returning the Affected Rows
- 8-12. Updating Large-Value Columns
- 8-13. Deleting Rows
- 8-14. Deleting Rows and Returning the Deleted Rows
- 8-15. Deleting All Rows Quickly (Truncating)
- 8-16. Merging Data (Inserting, Updating, and/or Deleting Values)
- 8-17. Inserting Output Data
- 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-4. Locating Characters in a String
- 9-5. Determining the Similarity of Strings
- 9-6. Returning the Leftmost or Rightmost 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 Lowercase 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-13. Include Missing Dates
- 10-14. Finding Arbitrary Dates
- 10-15. Querying for Intervals
- 10-16. Working with Dates and Times Across National Boundaries
- Chapter 11: Working with Numbers
- 11-1. Representing Integers
- 11-2. Creating Single-Bit Integers
- 11-3. Representing Decimal and Monetary Amounts
- 11-4. Representing Floating-Point Values
- 11-5. Writing Mathematical Expressions
- 11-6. Casting Between Data Types
- 11-7. Converting Numbers to Text
- 11-8. Converting from Text to a Number
- 11-9. Rounding
- 11-10. Rounding Always Up or Down
- 11-11. Discarding Decimal Places
- 11-12. Testing Equality of Binary Floating-Point Values
- 11-13. Treating Nulls as Zeros
- 11-14. Generating a Row Set of Sequential Numbers
- 11-15. Generating Random Integers in a Row Set
- 11-16. Reducing Space Used by Decimal Storage
- Chapter 12: Transactions, Locking, Blocking, and Deadlocking
- Transaction Control
- 12-1. Using Explicit Transactions
- 12-2. Displaying the Oldest Active Transaction
- 12-3. Querying Transaction Information by Session
- Locking
- 12-4. Viewing Lock Activity
- 12-5. Controlling a Table’s Lock-Escalation Behavior
- Transaction, Locking, and Concurrency
- 12-6. Configuring a Session’s Transaction-Locking Behavior
- Blocking
- 12-7. Identifying and Resolving Blocking Issues
- 12-8. Configuring How Long a Statement Will Wait for a Lock to Be Released
- Deadlocking
- 12-9. Identifying Deadlocks with a Trace Flag
- 12-10. Identifying Deadlocks with Extended Events
- 12-11. Setting Deadlock Priority
- Chapter 13: Managing Tables
- 13-1. Creating a Table
- 13-2. Adding a Column
- 13-3. Adding a Column that Requires Data
- 13-4. Changing a Column
- 13-5. Creating a Computed Column
- 13-6. Removing a Column
- 13-7. Removing a Table
- 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-12. Allowing Data Modifications to Foreign Key Columns in the Referenced Table to Be Reflected in the Referencing Table
- 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-16. Removing 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
- Chapter 14: Managing Views
- Regular Views
- 14-1. Creating a View
- 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-6. Modifying a View
- 14-7. Modifying Data Through a View
- 14-8. Encrypting a View
- 14-9. Indexing a View
- 14-10. Creating a Partitioned View
- 14-11. Creating a Distributed-Partitioned View
- Chapter 15: Managing Large Tables and Databases
- 15-1. Partitioning a Table
- 15-2. Locating Data in a Partition
- 15-3. Adding a Partition
- 15-4. Removing 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. Determining the NEXT USED Partition
- 15-9. Moving a Partition to a Different Partitioned Table
- 15-10. Moving Data from a Nonpartitioned Table to a Partition in a Partitioned Table
- 15-11. Moving a Partition from a Partitioned Table to a Nonpartitioned Table
- 15-12. Reducing Table Locks on Partitioned Tables
- 15-13. Removing Partition Functions and Schemes
- 15-14. Easing VLDB Manageability (with Filegroups)
- 15-15. Compressing Table Data
- 15-16. Rebuilding a Heap
- Chapter 16: Managing Indexes
- Index Overview
- 16-1. Creating a Table Index
- 16-2. Creating a Table Index
- 16-3. Enforcing Uniqueness on Non-key Columns
- 16-4. Creating an Index on Multiple Columns
- 16-5. Defining Index Column Sort Direction
- 16-6. Viewing Index Metadata
- 16-7. Disabling an Index
- 16-8. Dropping Indexes
- 16-9. Changing an Existing Index
- Controlling Index Build Performance and Concurrency
- 16-10. Sorting in Tempdb
- 16-11. Controlling Index Creation Parallelism
- 16-12. User Table Access During Index Creation
- Index Options
- 16-13. Using an Index INCLUDE
- 16-14. Using PADINDEX and FILLFACTOR
- 16-15. Disabling Page and/or Row Index Locking
- Managing Very Large Indexes
- 16-16. Creating an Index on a Filegroup
- 16-17. Implementing Index Partitioning
- 16-18. Indexing a Subset of Rows
- 16-19. Reducing Index Size
- 16-20. Further Reducing Index Size
- Chapter 17: Stored Procedures
- 17-1. Creating a Stored Procedure
- 17-2. Passing Parameters
- 17-3. Making Parameters Optional
- 17-4. Making Early Parameters Optional
- 17-5. Returning Output
- 17-6. Modifying a Stored Procedure
- 17-7. Removing a Stored Procedure
- 17-8. Automatically Run a Stored Procedure at Start-Up
- 17-9. Viewing a Stored Procedure’s Definition
- 17-10. Documenting Stored Procedures
- 17-11. Determining the Current Nesting Level
- 17-12. Encrypting a Stored Procedure
- 17-13. Specifying a Security Context
- 17-14. Avoiding Cached Query Plans
- 17-15. Flushing the Procedure Cache
- Chapter 18: User-Defined Functions and Types
- UDF Basics
- 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-5. Viewing UDF Metadata
- Benefitting from UDFs
- 18-6. Maintaining Reusable Code
- 18-7. Cross-Referencing Natural Key Values
- 18-8. Replacing a View with a Function
- 18-9. Dropping a Function
- UDT Basics
- 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
- Chapter 19: In-Memory OLTP
- 19-1. Configuring a Database So That It Can Utilize In-Memory OLTP
- 19-2. Making a Memory-Optimized Table
- 19-3. Creating a Memory-Optimized Table Variable
- 19-4. Creating a Natively Compiled Stored Procedure
- 19-5. Determining Which Database Objects Are Configured to Use In-Memory OLTP
- 19-6. Determining Which Objects Are Actively Using In-Memory OLTP on the Server
- 19-7. Detecting Performance Issues with Natively Compiled Stored Procedure Parameters
- 19-8. Viewing CFP Metadata
- 19-9. Disabling or Enabling Automatic Merging
- 19-10. Manually Merging Checkpoint File Pairs
- Chapter 20: Triggers
- 20-1. Creating an AFTER DML Trigger
- 20-2. Creating an INSTEAD OF DML Trigger
- 20-3. Handling Transactions in Triggers
- 20-4. Linking Trigger Execution to Modified Columns
- 20-5. Viewing DML Trigger Metadata
- 20-6. Creating a DDL Trigger
- 20-7. Creating a Logon Trigger
- 20-8. Viewing DDL Trigger Metadata
- 20-9. Modifying a Trigger
- 20-10. Enabling and Disabling a Trigger
- 20-11. Nesting Triggers
- 20-12. Controlling Recursion
- 20-13. Specifying the Firing Order
- 20-14. Dropping a Trigger
- Chapter 21: Error Handling
- 21-1. Handling Batch Errors
- 21-2. What Are the Error Numbers and Messages Within SQL?
- 21-3. How Can I Implement Structured Error Handling in My Queries?
- 21-4. How Can I Use Structured Error Handling, but Still Return an Error?
- 21-5. Nested Error Handling
- 21-6. Throwing an Error
- 21-7. Creating a User-Defined Error
- 21-8. Removing a User-Defined Error
- Chapter 22: Query Performance Tuning
- Query Performance Tips
- Capturing and Evaluating Query Performance
- 22-1. Capturing Executing Queries
- 22-2. Viewing Estimated Query Execution Plans
- 22-3. Viewing Execution Runtime Information
- 22-4. Viewing Statistics for Cached Plans
- 22-5. Viewing Record Counts for Cached Plans
- 22-6. Viewing Aggregated Performance Statistics Based on Query or Plan Patterns
- 22-7. Identifying the Top Bottleneck
- 22-8. Identifying I/O Contention by Database and File
- Miscellaneous Techniques
- 22-9. Parameterizing Ad Hoc Queries
- 22-10. Forcing the Use of a Query Plan
- 22-11. Applying Hints Without Modifying a SQL Statement
- 22-12. Creating Plan Guides from Cache
- 22-13. Checking the Validity of a Plan Guide
- 22-14. Parameterizing a Nonparameterized Query Using Plan Guides
- 22-15. Limiting Competing Query Resource Consumption
- Chapter 23: Hints
- 23-1. Forcing a Join’s Execution Approach
- 23-2. Forcing a Statement Recompile
- 23-3. Executing a Query Without Locking
- 23-4. Forcing an Index Seek
- 23-5. Forcing an Index Scan
- 23-6. Optimizing for First Rows
- 23-7. Specifying Join Order
- 23-8. Forcing the Use of a Specific Index
- 23-9. Optimizing for Specific Parameter Values
- Chapter 24: Index Tuning and Statistics
- Index Tuning
- Index Maintenance
- 24-1. Displaying Index Fragmentation
- 24-2. Rebuilding Indexes
- 24-3. Defragmenting Indexes
- 24-4. Rebuilding a Heap
- 24-5. Displaying Index Usage
- Statistics
- 24-6. Manually Creating Statistics
- 24-7. Creating Statistics on a Subset of Rows
- 24-8. Updating Statistics
- 24-9. Generating Statistics Across All Tables
- 24-10. Updating Statistics Across All Tables
- 24-11. Viewing Statistics Details
- 24-12. Removing Statistics
- 24-13. Finding When Stats Need to Be Created
- Chapter 25: XML
- 25-1. Creating an XML Column
- 25-2. Inserting XML Data
- 25-3. Validating XML Data
- 25-4. Verifying the Existence of XML Schema Collections
- 25-5. Retrieving XML Data
- 25-6. Modifying XML Data
- 25-7. Indexing XML Data
- 25-8. Formatting Relational Data as XML
- 25-9. Formatting XML Data as Relational
- 25-10. Using XML to Return a Delimited String
- Chapter 26: Files, Filegroups, and Integrity
- 26-1. Adding a Data File or a Log File
- 26-2. Retrieving Information about the Files in a Database
- 26-3. Removing a Data File or a Log File
- 26-4. Relocating a Data File or a Log File
- 26-5. Changing a File’s Logical Name
- 26-6. Increasing the Size of a Database File
- 26-7. Adding a Filegroup
- 26-8. Adding a File to a Filegroup
- 26-9. Setting the Default Filegroup
- 26-10. Adding Data to a Specific Filegroup
- 26-11. Moving Data to a Different Filegroup
- 26-12. Removing a Filegroup
- 26-13. Making a Database or a Filegroup Read-Only
- 26-14. Viewing Database Space Usage
- 26-15. Shrinking the Database or a Database File
- 26-16. Checking the Consistency of Allocation Structures
- 26-17. Checking Allocation and Structural Integrity
- 26-18. Checking the Integrity of Tables in a Filegroup
- 26-19. Checking the Integrity of Specific Tables and Indexed Views
- 26-20. Checking Constraint Integrity
- 26-21. Checking System Table Consistency
- Chapter 27: Backup
- 27-1. Backing Up a Database
- 27-2. Compressing a Backup
- 27-3. Ensuring That a Backup Can Be Restored
- 27-4. Transaction Log Backup
- 27-5. Understanding Why the Transaction Log Continues to Grow
- 27-6. Performing a Differential Backup
- 27-7. Backing Up a Single Row or Table
- 27-8. Creating a Database Snapshot
- 27-9. Backing Up Data Files or Filegroups
- 27-10. Mirroring Backup Files
- 27-11. Backing Up a Database Without Affecting the Normal Sequence of Backups
- 27-12. Querying Backup Data
- 27-13. Encrypting a Backup
- 27-14. Compressing an Encrypted Backup
- 27-15. Backing Up Certificates
- 27-16. Backing Up to Azure
- 27-17. Backing Up to Multiple Files
- Chapter 28: Recovery
- 28-1. Restoring a Database from a Full Backup
- 28-2. Restoring a Database from a Transaction Log Backup
- 28-3. Restoring a Database from a Differential Backup
- 28-4. Restoring a File or Filegroup
- 28-5. Performing a Piecemeal (PARTIAL) Restore
- 28-6. Restoring a Page
- 28-7. Identifying Databases with Multiple Recovery Paths
- 28-8. Restore a Single Row or Table
- 28-9. Recover from a Backup in Azure Blob Storage
- 28-10. Recover a Certificate
- Chapter 29: Principals and Users
- Windows Principals
- 29-1. Creating a Windows Login
- 29-2. Viewing Windows Logins
- 29-3. Altering a Windows Login
- 29-4. Dropping a Windows Login
- 29-5. Denying SQL Server Access to a Windows User or Group
- SQL Server Principals
- 29-6. Creating a SQL Server Login
- 29-7. Viewing SQL Server Logins
- 29-8. Altering a SQL Server Login
- 29-9. Managing a Login’s Password
- 29-10. Dropping a SQL Login
- 29-11. Managing Server Role Members
- 29-12. Reporting Fixed Server Role Information
- Database Principals
- 29-13. Creating Database Users
- 29-14. Reporting Database User Information
- 29-15. Modifying a Database User
- 29-16. Removing a Database User from the Database
- 29-17. Fixing Orphaned Database Users
- 29-18. Reporting Fixed Database Roles Information
- 29-19. Managing Fixed Database Role Membership
- 29-20. Managing User-Defined Database Roles
- 29-21. Managing Application Roles
- 29-22. Managing User-Defined Server Roles
- Chapter 30: Securables, Permissions, and Auditing
- Permissions Overview
- 30-1. Reporting SQL Server Assignable Permissions
- Server-Scoped Securables and Permissions
- 30-2. Managing Server Permissions
- 30-3. Querying Server-Level Permissions
- Database-Scoped Securables and Permissions
- 30-4. Managing Database Permissions
- 30-5. Querying Database Permissions
- Schema-Scoped Securables and Permissions
- 30-6. Managing Schemas
- 30-7. Managing Schema Permissions
- Object Permissions
- 30-8. Managing Object Permissions
- Managing Permissions Across Securable Scopes
- 30-9. Determining Permissions to a Securable
- 30-10. Reporting Permissions by Securable Scope
- 30-11. Changing Securable Ownership
- 30-12. Allowing Access to Non-SQL Server Resources
- Auditing Activity of Principals Against Securables
- 30-13. Defining Audit Data Sources
- 30-14. Capturing SQL Instance–Scoped Events
- 30-15. Capturing Database-Scoped Events
- 30-16. Querying Captured Audit Data
- 30-17. Managing, Modifying, and Removing Audit Objects
- Chapter 31: Objects and Dependencies
- Index
I dedicate this book to the memory of my recently-departed grandfather Dan L Greenland who was an inspiration and example in so many ways. He was a great leader and a very loving grandfather to me.
—Jason L Brimhall
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.