SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

Book description

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, 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, developing triggers, and applying aggregate functions. 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: Newly updated for SQL Server 2012

  • Comprehensive: Covers all common T-SQL problem domains

  • What you'll learn

  • Create databases, tables, and indexes

  • Query and manipulate data

  • Store and manage XML inside the database

  • Move business logic into the database

  • Resolve common performance problems.

  • Build reports that matter to your business.

  • Perform common backup and recovery tasks.

  • Who this book is for

    SQL Server 2012 T-SQL Recipes is aimed at technically-oriented users of SQL Server desiring to extract the full power of the platform through it's powerful, built-in programming and scripting language. Target readers include developers who use Microsoft SQL Server 2012 as their back-end database, and the database administrators who create, manage, and secure those databases.

    Table of contents

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

    Product information

    • Title: SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach
    • Author(s):
    • Release date: September 2012
    • Publisher(s): Apress
    • ISBN: 9781430242000