SQL Server T-SQL Recipes, Fourth Edition

Book description

SQL Server T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server. This edition has been lightly updated for SQL Server 2014 and provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, create in-memory tables and stored procedures, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on.

Solutions in this book are divided into chapters by problem domain. Each chapter is a collection of solutions around a single facet of the language such as writing queries, managing indexes, error handling, and query performance. Each solution is presented code-first, giving you a working code example to copy from and implement immediately in your own environment. Following each example is an in-depth description of how and why the given solution works. Tradeoffs and alternative approaches are also discussed.

  • • Focused on solutions: Look up what you need to do. Learn how to do it. Do it.
  • • Current: Lightly updated for SQL Server 2014
  • • Comprehensive: Covers all common T-SQL problem domains
  • Table of contents

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

    Product information

    • Title: SQL Server T-SQL Recipes, Fourth Edition
    • Author(s):
    • Release date: July 2015
    • Publisher(s): Apress
    • ISBN: 9781484200612