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
- 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
Product information
- Title: SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach
- Author(s):
- Release date: September 2012
- Publisher(s): Apress
- ISBN: 9781430242000
You might also like
book
Microsoft® SQL Server® 2012 T-SQL Fundamentals
Gain a solid understanding of T-SQL—and write better queries Master the fundamentals of Transact-SQL—and develop your …
book
SQL Server 2014 with PowerShell v5 Cookbook
Over 150 real-world recipes to simplify database management, automate repetitive tasks, and enhance your productivity About …
book
Expert Scripting and Automation for SQL Server DBAs
Automate your workload and manage more databases and instances with greater ease and efficiency by combining …
book
SQL Server DMVs in Action
SQL Server DMVs in Action is a practical guide that shows you how to obtain, interpret, …