Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
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
24-1. Creating an XML Column
24-2. Inserting XML Data
24-3. Validating XML Data
24-4. Verifying the Existence of XML Schema Collections
24-5. Retrieving XML Data
24-6. Modifying XML Data
24-7. Indexing XML Data
24-8. Formatting Relational Data as XML
24-9. Formatting XML Data as Relational
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
30-1. Changing the Name of a Database Object
30-2. Changing an Object’s Schema
30-3. Identifying Object Dependencies
30-4. Identifying Referencing and Referenced Entities
30-5. Viewing an Object’s Definition
30-6. Returning a Database Object’s Name, Schema Name, and Object ID
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset