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