book

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

Name: SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach
ISBN: 9781430242000

by Jason Brimhall, David Dye, Jonathan Gennick, Andy Roberts, Wayne Sheffield

September 2012

Intermediate to advanced

794 pages

19h 4m

English

Apress

Read now

Unlock full access

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 Database1-2. Retrieving Specific Columns1-3. Retrieving All Columns1-4. Specifying the Rows to Be Returned1-5. Renaming the Output Columns1-6. Building a Column from an Expression1-7. Providing Shorthand Names for Tables1-8. Negating a Search Condition1-9. Specifying A Range of Values1-10. Checking for NULL Values1-11. Providing a List of Values1-12. Performing Wildcard Searches1-13. Sorting Your Results1-14. Specifying Sort Order1-15. Sorting by Columns Not Selected1-16. Forcing Unusual Sort Orders1-17. Paging Through A Result Set
Chapter 2: Elementary Programming
2-1. Declaring Variables2-2. Retrieving a Value into a Variable2-3. Writing an IF…THEN…ELSE Statement2-4. Writing a Simple CASE Expression2-5. Writing a Searched CASE Expression2-6. Writing a WHILE Statement2-7. Returning from the Current Execution Scope2-8. Going to a Label in a Transact-SQL Batch2-9. Pausing Execution for a Period of Time2-10. Creating and Using Cursors
Chapter 3: NULLs and Other Pitfalls
3-1. Replacing NULL with an Alternate Value3-2. Returning the First Non-NULL Value from a List3-3. Choosing Between ISNULL and COALESCE in a SELECT Statement3-4. Looking for NULLs in a Table3-5. Removing Values from an Aggregate3-6. Enforcing Uniqueness with NULL Values3-7. Enforcing Referential Integrity on Nullable Columns3-8. Joining Tables on Nullable Columns

Chapter4: Querying from Multiple Tables
4-1. Correlating Parent and Child Rows4-2. Querying Many-to-Many Relationships4-3. Making One Side of a Join Optional4-4. Making Both Sides of a Join Optional4-5. Generating All Possible Row Combinations4-6. Selecting from a Result Set4-7. Testing for the Existence of a Row4-8. Testing Against the Result from a Query4-9. Comparing Subsets of a Table4-10. Stacking Two Row Sets Vertically4-11. Eliminating Duplicate Values from a Union4-12. Subtracting One Row Set from Another4-13. Finding Rows in Common Between Two Row Sets4-14. Finding Rows That Are Missing4-15. Comparing Two Tables
Chapter 5: Grouping and Summarizing
5-1. Summarizing a Result Set5-2. Creating Summary Groups5-3. Restricting a Result Set to Groups of Interest5-4. Removing Duplicates from the Detailed Results5-5. Creating Summary Cubes5-6. Creating Hierarchical Summaries5-7. Creating Custom Summaries5-8. Identifying Rows Generated by the GROUP BY Arguments5-9. Identifying Summary Levels
Chapter 6: Advanced Select Techniques
6-1. Avoiding Duplicate Results6-2. Returning the Top N Rows6-3. Renaming a Column in the Output6-4. Retrieving Data Directly into Variables6-5. Creating a New Table with the Results from a Query6-6. Filtering on the Results from a Subquery6-7. Selecting from the Results of Another Query6-8. Passing Rows Through a Function6-9. Returning Random Rows from a Table6-10. Converting Rows into Columns6-11. Converting Columns into Rows6-12. Reusing Common Subqueries in a Query6-13. Querying Recursive Tables6-14. Hard-Coding the Results from a Query
Chapter 7: Aggregations and Windowing
7-1. Computing an Average7-2. Counting the Rows in a Group7-3. Summing the Values in a Group7-4. Finding the High and Low Values in a Group7-5. Detecting Changes in a Table7-6. Finding the Statistical Variance in the Values of a Column7-7. Finding the Standard Deviation in the Values of a Column7-8. Calculating Totals Based Upon the Prior Row7-9. Calculating Totals Based Upon a Subset of Rows7-10. Using a Logical Window7-11. Generating an Incrementing Row Number7-12. Returning Rows by Rank7-13. Returning Rows by Rank Without Gaps7-14. Sorting Rows into Buckets7-15. Grouping Logically Consec44utive Rows Together7-16. Accessing Values from Other Rows7-17. Accessing the First or Last Value from a Partition7-18. Calculating the Relative Position or Rank of a Value in a Set of Values7-19. Calculating Continuous or Discrete Percentiles7-20. Assigning Sequences in a Specified Order
Chapter 8: Inserting, Updating, Deleting
8-1. Inserting a New Row8-2. Specifying Default Values8-3. Overriding an IDENTITY Column8-4. Generating a Globally Unique Identifier (GUID)8-5. Inserting Results from a Query8-6. Inserting Results from a Stored Procedure8-7. Inserting Multiple Rows at Once8-8. Inserting Rows and Returning the Inserted Rows8-9. Updating a Single Row or Set of Rows8-10. Updating with a Sec29ond Table as the Data Source8-11. Updating Data and Returning the Affected Rows8-12. Updating Large-Value Columns8-13. Deleting Rows8-14. Deleting Rows and Returning the Deleted Rows8-15. Deleting All Rows Quickly (Truncating)8-16. Merging Data (Inserting, Updating, or Deleting Values)
Chapter 9: Working with Strings
9-1. Concatenating Multiple Strings9-2. Finding a Character’s ASCII Value9-3. Returning Integer and Character Unicode Values9-4. Locating a Substring9-5. Determining the Similarity of Strings9-6. Returning the Left-Most Portion of a String9-7. Returning Part of a String9-8. Counting Characters or Bytes in a String9-9. Replacing Part of a String9-10. Stuffing a String into a String9-11. Changing Between Lower- and Uppercase9-12. Removing Leading and Trailing Blanks9-13. Repeating an Expression N Times9-14. Repeating a Blank Space N Times9-15. Reversing the order of Characters in a String
Chapter 10: Working with Dates and Times
10-1. Returning the Current Date and Time10-2. Converting Between Time Zones10-3. Converting a Date/Time Value to a Datetimeoffset Value10-4. Incrementing or Decrementing a Date’s Value10-5. Finding the Difference Between Two Dates10-6. Finding the Elapsed Time Between Two Dates10-7. Displaying the String Value for Part of a Date10-8. Displaying the Integer Representations for Parts of a Date10-9. Determining Whether a String Is a Valid Date10-10. Determining the Last Day of the Month10-11. Creating a Date from Numbers10-12. Finding the Beginning Date of a Datepart10-13. Include Missing Dates10-14. Finding Arbitrary Dates10-15. Querying for Intervals10-16. Working with Dates and Times Across National Boundaries
Chapter 11: Working with Numbers
11-1. Representing Integers11-2. Representing Decimal Amounts11-3. Representing Monetary Amounts11-4. Representing Floating-Point Values11-5. Writing Mathematical Expressions11-6. Guarding Against Errors in Expressions with Mixed Data Types11-7. Rounding11-8. Rounding Always Up or Down11-9. Discarding Decimal Places11-10. Testing Equality of Binary Floating-Point Values11-11. Treating Nulls as Zeros11-12. Generating a Row Set of Sequential Numbers11-13. Generating Random Integers in a Row Set11-14. Reducing Space Used by Decimal Storage
Chapter 12: Transactions, Locking, Blocking, and Deadlocking
Transaction Control12-1. Using Explicit Transactions12-2. Displaying the Oldest Active Transaction12-3. Querying Transaction Information by SessionLocking12-4. Viewing Lock Activity12-5. Controlling a Table’s Lock Escalation BehaviorTransaction, Locking, and Concurrency12-6. Configuring a Session’s Transaction Locking BehaviorBlocking12-7. Identifying and Resolving Blocking Issues12-8. Configuring How Long a Statement Will Wait for a Lock to Be ReleasedDeadlocking12-9. Identifying Deadlocks with a Trace Flag12-10. Setting Deadlock Priority
Chapter 13: Managing Tables
13-1. Creating a Table13-2. Adding a Column13-3. Adding a Column That Requires Data13-4. Changing a Column13-5. Creating a Computed Column13-6. Removing a Column13-7. Removing a Table13-8. Reporting on a Table’s Definition13-9. Reducing Storage Used by NULL Columns13-10. Adding a Constraint to a Table13-11. Creating a Recursive Foreign Key13-12. Allowing Data Modifications to Foreign Keys Columns in the Referenced Table to Be Reflected in the Referencing Table13-13. Specifying Default Values for a Column13-14. Validating Data as It Is Entered into a Column13-15. Temporarily Turning Off a Constraint13-16. Removing a Constraint13-17. Creating Auto-incrementing Columns13-18. Obtaining the Identity Value Used13-19. Viewing or Changing the Seed Settings on an Identity Column13-20. Inserting Values into an Identity Column13-21. Automatically Inserting Unique Values13-22. Using Unique Identifiers Across Multiple Tables13-23. Using Temporary Storage
Chapter 14: Managing Views
Regular Views14-1. Creating a View14-2. Querying a View’s Definition14-3. Obtaining a List of All Views in a Database14-4. Obtaining a List of All Columns in a View14-5. Refreshing the Definition of a View14-6. Modifying a View14-7. Modifying Data Through a View14-8. Encrypting a View14-9. Indexing a View14-10. Creating a Partitioned View14-11. Creating a Distributed Partitioned View
Chapter 15: Managing Large Tables and Databases
15-1. Partitioning a Table15-2. Locating Data in a Partition15-3. Adding a Partition15-4. Removing a Partition15-5. Determining Whether a Table Is Partitioned15-6. Determining the Boundary Values for a Partitioned Table15-7. Determining the Partitioning Column for a Partitioned Table15-8. Moving a Partition to a Different Partitioned Table15-9. Moving Data from a Nonpartitioned Table to a Partition in a Partitioned Table15-10. Moving a Partition from a Partitioned Table to a Nonpartitioned Table15-11. Reducing Table Locks on Partitioned Tables15-12. Removing Partition Functions and Schemes15-13. Easing VLDB Manageability (with Filegroups)15-14. Compressing Table Data15-15. Rebuilding a Heap
Chapter 16: Managing Indexes
Index Overview16-1. Creating a Table Index16-2. Enforcing Uniqueness on Non-key Columns16-3. Creating an Index on Multiple Columns16-4. Defining Index Column Sort Direction16-5. Viewing Index Metadata16-6. Disabling an Index16-7. Dropping Indexes16-8. Changing an Existing IndexControlling Index Build Performance and Concurrency16-9. Sorting in Tempdb16-10. Controlling Index Creation Parallelism16-11. User Table Access During Index CreationIndex Options16-12. Using an Index INCLUDE16-13. Using PADINDEX and FILLFACTOR16-14. Disabling Page and/or Row Index LockingManaging Very Large Indexes16-15. Creating an Index on a Filegroup16-16. Implementing Index Partitioning16-17. Indexing a Subset of Rows16-18. Reducing Index Size
Chapter 17: Stored Procedures
17-1. Selling the Benefits17-2. Creating a Stored Procedure17-3. Generalizing a Stored Procedure17-4. Making Parameters Optional17-5. Making Early Parameters Optional17-6. Returning Output17-7. Modifying a Stored Procedure17-8. Removing a Stored Procedure17-9. Automatically Run a Stored Procedure at Start-Up17-10. Viewing a Stored Procedure’s Definition17-11. Documenting Stored Procedures17-12. Determining the Current Nesting Level17-13. Encrypting a Stored Procedure17-14. Specifying a Security Context17-15. Avoiding Cached Query Plans17-16. Flushing the Procedure Cache
Chapter 18: User-Defined Functions and Types
UDF Basics18-1. Creating Scalar Functions18-2. Creating Inline Functions18-3. Creating Multi-Statement User-Defined Functions18-4. Modifying User-Defined Functions18-5. Viewing UDF MetadataBenefitting from UDFs18-6. Maintaining Reusable Code18-7. Cross-Referencing Natural Key Values18-8. Replacing a View with a Function18-9. Dropping a Function18-10. Creating and Using User-Defined Types18-11. Identifying Dependencies on User-Defined Types18-12. Passing Table-Valued Parameters18-13. Dropping User-Defined Types
Chapter 19: Triggers
19-1. Creating an AFTER DML Trigger19-2. Creating an INSTEAD OF DML Trigger19-3. Handling Transactions in Triggers19-4. Linking Trigger Execution to Modified Columns19-5. Viewing DML Trigger Metadata19-6. Creating a DDL Trigger19-7. Creating a Logon Trigger19-8. Viewing DDL Trigger Metadata19-9. Modifying a Trigger19-10. Enabling and Disabling a Trigger19-11. Nesting Triggers19-12. Controlling Recursion19-13. Specifying the Firing Order19-14. Dropping a Trigger
Chapter 20: Error Handling
20-1. Handling batch errors20-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 handling20-6. Throwing an error20-7. Creating a user defined error20-7. Removing a user defined error
Chapter 21: Query Performance Tuning
Query Performance TipsCapturing and Evaluating Query Performance21-1. Capturing Executing Queries21-2. Viewing Estimated Query Execution Plans21-3. Viewing Execution Runtime Information21-4. Viewing Statistics for Cached Plans21-5. Viewing Record Counts for Cached Plans21-6. Viewing Aggregated Performance Statistics Based on Query or Plan Patterns21-7. Identifying the Top Bottleneck21-8. Identifying I/O Contention by Database and FileMiscellaneous Techniques21-9. Parameterizing Ad Hoc Queries21-10. Forcing Use of a Query Plan21-11. Applying Hints Without Modifying a SQL Statement21-12. Creating Plan Guides from Cache21-13. Checking the Validity of a Plan Guide21-14. Parameterizing a Nonparameterized Query Using Plan Guides21-15. Limiting Competing Query Resource Consumption
Chapter 22: Hints
22-1. Forcing a Join’s Execution Approach22-2. Forcing a Statement Recompile22-3. Executing a Query Without Locking22-4. Forcing an Index Seek22-5. Forcing an Index Scan22-6. Optimizing for First Rows22-7. Specifying Join Order22-8. Forcing Use of a Specific Index22-9. Optimizing for Specific Parameter Values
Chapter 23: Index Tuning and Statistics
Index TuningIndex Maintenance23-1. Displaying Index Fragmentation23-2. Rebuilding Indexes23-3. Defragmenting Indexes23-4. Rebuilding a Heap23-5. Displaying Index UsageStatistics23-6. Manually Creating Statistics23-7. Creating Statistics on a Subset of Rows23-8. Updating Statistics23-9. Generating Statistics Across All Tables23-10. Updating Statistics Across All Tables23-11. Viewing Statistics Details23-12. Removing Statistics
Chapter 24: XML
24-1. Creating an XML Column24-2. Inserting XML Data24-3. Validating XML Data24-4. Verifying the Existence of XML Schema Collections24-5. Retrieving XML Data24-6. Modifying XML Data24-7. Indexing XML Data24-8. Formatting Relational Data as XML24-9. Formatting XML Data as Relational
Chapter 25: Files, Filegroups, and Integrity
25-1. Adding a Data File or a Log File25-2. Removing a Data File or a Log File25-3. Relocating a Data File or a Log File25-4. Changing a File’s Logical Name25-5. Increasing the Size of a Database File25-6. Adding a Filegroup25-7. Adding a File to a Filegroup25-8. Setting the Default Filegroup25-9. Adding Data to a Specific Filegroup25-10. Moving Data to a Different Filegroup25-11. Removing a Filegroup25-12. Making a Database or a Filegroup Read-OnlyProblem #225-13. Viewing Database Space Usage25-14. Shrinking the Database or a Database File25.15. Checking Consistency of Allocation Structures25-16. Checking Allocation and Structural Integrity25-17. Checking Integrity of Tables in a Filegroup25-18. Checking Integrity of Specific Tables and Indexed Views25-19. Checking Constraint Integrity25-20. Checking System Table Consistency
Chapter 26: Backup
26-1. Backing Up a Database26-2. Compressing a Backup26-3. Ensuring That a Backup Can Be Restored26-4. Understanding Why the Transaction Log Continues to Grow26-5. Performing a Differential Backup26-6. Backing Up a Single Row or Table26-7. Backing Up Data Files or Filegroups26-8. Mirroring Backup Files26-9. Backing Up a Database Without Affecting the Normal Sequence of Backups26-10. Querying Backup Data
Chapter 27: Recovery
27-1. Restoring a Database from a Full Backup27-2. Restoring a Database from a Transaction Log Backup27-3. Restoring a Database from a Differential Backup27-4. Restoring a File or Filegroup27-5. Performing a Piecemeal (PARTIAL) Restore27-6. Restoring a Page27-7. Identifying Databases with Multiple Recovery Paths
Chapter 28: Principals and Users
Windows Principals28-1. Creating a Windows Login28-2. Viewing Windows Logins28-3. Altering a Windows Login28-4. Dropping a Windows Login28-5. Denying SQL Server Access to a Windows User or GroupSQL Server Principals28-6. Creating a SQL Server Login28-7. Viewing SQL Server Logins28-8. Altering a SQL Server Login28-9. Managing a Login’s Password28-10. Dropping a SQL Login28-11. Managing Server Role Members28-12. Reporting Fixed Server Role InformationDatabase Principals28-13. Creating Database Users28-14. Reporting Database User Information28-15. Modifying a Database User28-16. Removing a Database User from the Database28-17. Fixing Orphaned Database Users28-18. Reporting Fixed Database Roles Information28-19. Managing Fixed Database Role Membership28-20. Managing User-Defined Database Roles28-21. Managing Application Roles
Chapter 29: Securables, Permissions, and Auditing
Permissions Overview29-1. Reporting SQL Server Assignable PermissionsServer-Scoped Securables and Permissions29-2. Managing Server Permissions29-3. Querying Server-Level PermissionsDatabase-Scoped Sec14urables and Permissions29-4. Managing Database Permissions29-5. Querying Database PermissionsSchema-Scoped Securables and Permissions29-6. Managing Schemas29-7. Managing Schema PermissionsObject Permissions29-8. Managing Object PermissionsManaging Permissions Across Securable Scopes29-9. Determining Permissions to a Securable29-10. Reporting Permissions by Securable Scope29-11. Changing Securable Ownership29-12. Allowing Access to Non-SQL Server ResourcesAuditing Activity of Principals Against Securables29-13. Defining Audit Data Sources29-14. Capturing SQL Instance–Scoped Events29-15. Capturing Database-Scoped Events29-16. Querying Captured Audit Data29-17. Managing, Modifying, and Removing Audit Objects
Chapter 30: Objects and Dependencies
30-1. Changing the Name of a Database Object30-2. Changing an Object’s Schema30-3. Identifying Object Dependencies30-4. Identifying Referencing and Referenced Entities30-5. Viewing an Object’s Definition30-6. Returning a Database Object’s Name, Schema Name, and Object ID
Index

Overview

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft® SQL Server® 2012 T-SQL Fundamentals

Publisher Resources

ISBN: 9781430242000Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills