book

SQL Server T-SQL Recipes, Fourth Edition

Name: SQL Server T-SQL Recipes, Fourth Edition
ISBN: 9781484200612

by Jason Brimhall, Jonathan Gennick, Wayne Sheffield

July 2015

Intermediate to advanced

972 pages

21h 39m

English

Apress

Read now

Unlock full access

Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
About the Authors
About the Techincal reviewer
Acknowledgments
Introduction

Chapter 1: Getting Started with SELECT
1-1. Connecting to a DatabaseProblemSolutionHow It Works1-2. Checking the Database Server VersionProblemSolutionHow It Works1-3. Checking the Database NameProblemSolutionHow It Works1-4. Checking Your UsernameProblemSolutionHow It Works1-5. Querying a TableProblemSolutionHow It Works1-6. Returning Specific RowsProblemSolutionHow It Works1-7. Listing the Available TablesProblemSolutionHow It Works1-8. Naming the Output ColumnsProblemSolutionHow It Works1-9. Providing Shorthand Names for TablesProblemSolutionHow It Works1-10. Computing New Columns from Existing DataProblemSolutionHow It Works1-11. Negating a Search ConditionProblemSolutionHow It Works1-12. Keeping the WHERE Clause UnambiguousProblemSolutionHow It Works1-13. Testing for ExistenceProblemSolutionHow It Works1-14. Specifying a Range of ValuesProblemSolutionHow It Works1-15. Checking for Null ValuesProblemSolutionHow It Works1-16. Writing an IN-ListProblemSolutionHow It Works1-17. Performing Wildcard SearchesProblemSolutionHow It Works1-18. Sorting Your ResultsProblemSolutionHow It Works1-19. Specifying the Case-Sensitivity of a SortProblemSolutionHow It Works1-20. Sorting Nulls High or LowProblemSolutionHow It Works1-21. Forcing Unusual Sort OrdersProblemSolutionHow It Works1-22. Paging Through a Result SetProblemSolutionHow It Works1-23. Sampling a Subset of RowsProblemSolutionHow It Works
Chapter 2: Elementary Programming
2-1. Executing T-SQL from a FileProblemSolutionHow It Works2-2. Retrieving Values into VariablesProblemSolutionHow It Works2-3. Writing ExpressionsProblemSolutionHow It Works2-4. Deciding Between Two Execution PathsProblemSolutionHow It Works2-5. Detecting Whether Rows ExistProblemSolutionHow It Works2-6. Going to a Label in a Transact-SQL BatchProblemSolutionHow It Works2-7. Trapping and Throwing ErrorsProblemSolutionHow It Works2-8. Returning from the Current Execution ScopeProblemSolution #1: Exit with No Return ValueSolution #2: Exit and Provide a ValueHow It Works2-9. Writing a Simple CASE ExpressionProblemSolutionHow It Works2-10. Writing a Searched CASE ExpressionProblemSolutionHow It Works2-11. Repeatedly Executing a Section of CodeProblemSolutionHow It Works2-12. Controlling Iteration in a LoopProblemSolutionHow It Works2-13. Pausing Execution for a Period of TimeProblemSolutionHow It Works2-14. Looping through Query Results a Row at a TimeProblemSolutionHow It Works
Chapter 3: Working with NULLS
3-1. Replacing NULL with an Alternate ValueProblemSolutionHow It Works3-2. Returning the First Non-NULL Value from a ListProblemSolutionHow It Works3-3. Choosing Between ISNULL and COALESCE in a SELECT StatementProblemSolutionHow It Works3-4. Looking for NULLs in a TableProblemSolutionHow It Works3-5. Removing Values from an AggregateProblemSolutionHow It Works3-6. Enforcing Uniqueness with NULL ValuesProblemSolutionHow It Works3-7. Enforcing Referential Integrity on Nullable ColumnsProblemSolutionHow It Works3-8. Joining Tables on Nullable ColumnsProblemSolutionHow It Works
Chapter 4: Querying from Multiple Tables
4-1. Correlating Parent and Child RowsProblemSolutionHow It Works4-2. Querying Many-to-Many RelationshipsProblemSolutionHow It Works4-3. Making One Side of a Join OptionalProblemSolutionHow It Works4-4. Making Both Sides of a Join OptionalProblemSolutionHow It Works4-5. Generating All Possible Row CombinationsProblemSolutionHow It Works4-6. Selecting from a Result SetProblemSolutionHow It Works4-7. Introducing New ColumnsProblemSolutionHow It Works4-8. Testing for the Existence of a RowProblemSolutionHow It Works4-9. Testing Against the Result from a QueryProblemSolutionHow It Works4-10. Stacking Two Row Sets VerticallyProblemSolutionHow It Works4-11. Eliminating Duplicate Values from a UnionProblemSolutionHow It Works4-12. Subtracting One Row Set from AnotherProblemSolutionHow It Works4-13. Finding Rows in Common Between Two Row SetsProblemSolutionHow It Works4-14. Finding Rows that Are MissingProblemSolutionHow It Works4-15. Comparing Two TablesProblemSolutionHow It Works
Chapter 5: Aggregations and Grouping
5-1. Computing an AggregationProblemSolutionHow It Works5-2. Creating Aggregations Based upon the Values of the DataProblemSolutionHow It Works5-3. Counting the Rows in a GroupProblemSolutionHow It Works5-4. Detecting Changes in a TableProblemSolutionHow It Works5-5. Restricting a Result Set to Groups of InterestProblemSolutionHow It Works5-6. Performing Aggregations against Unique Values OnlyProblemSolutionHow It Works5-7. Creating Hierarchical SummariesProblemSolutionHow It Works5-8. Creating Summary Totals and SubtotalsProblemSolutionHow It Works5-9. Creating Custom SummariesProblemSolutionHow It Works5-10. Identifying Rows Generated by the GROUP BY ArgumentsProblemSolutionHow It Works5-11. Identifying Summary LevelsProblemSolutionHow It Works
Chapter 6: Advanced Select Techniques
6-1. Avoiding Duplicate ResultsProblemSolution #1Solution #2How It Works6-2. Returning the Top N RowsProblemSolutionHow It Works6-3. Renaming a Column in the OutputProblemSolutionHow It Works6-4. Retrieving Data Directly into VariablesProblemSolutionHow It Works6-5. Creating a New Table with the Results from a QueryProblemSolutionHow It Works6-6. Filtering the Results from a SubqueryProblemSolutionHow It Works6-7. Selecting from the Results of Another QueryProblemSolutionHow It Works6-8. Passing Rows Through a FunctionProblemSolutionHow It Works6-9. Returning Random Rows from a TableProblemSolutionHow It Works6-10. Converting Rows into ColumnsProblemSolutionHow It Works6-11. Converting Columns into RowsProblemSolutionHow It Works6-12. Reusing Common Subqueries in a QueryProblemSolutionHow It Works6-13. Querying Recursive TablesProblemSolutionHow It Works6-14. Hard-Coding the Results from a QueryProblemSolutionHow It Works
Chapter 7: Windowing Functions
7-1. Calculating Totals Based upon the Prior RowProblemSolutionHow It Works7-2. Calculating Totals Based upon a Subset of RowsProblemSolutionHow It Works7-3. Calculating a Percentage of TotalProblemSolutionHow It Works7-4. Calculating a “Row X of Y”ProblemSolutionHow It Works7-5. Using a Logical WindowProblemSolutionHow It Works7-6. Generating an Incrementing Row NumberProblemSolutionHow It Works7-7. Returning Rows by RankProblemSolutionHow It Works7-8. Sorting Rows into BucketsProblemSolutionHow It Works7-9. Grouping Logically Consecutive Rows TogetherProblemSolutionHow It Works7-10. Accessing Values from Other RowsProblemSolutionHow It Works7-11. Finding Gaps in a Sequence of NumbersProblemSolutionHow It Works7-12. Accessing the First or Last Value from a PartitionProblemSolutionHow It Works7-13. Calculating the Relative Position or Rank of a Value within a Set of ValuesProblemSolutionHow It Works7-14. Calculating Continuous or Discrete PercentilesProblemSolutionHow It Works7-15. Assigning Sequences in a Specified OrderProblemSolutionHow It Works
Chapter 8: Inserting, Updating, Deleting
8-1. Inserting a New RowProblemSolutionHow It Works8-2. Specifying Default ValuesProblemSolutionHow It Works8-3. Overriding an IDENTITY ColumnProblemSolutionHow It Works8-4. Generating a Globally Unique Identifier (GUID)ProblemSolutionHow It Works8-5. Inserting Results from a QueryProblemSolutionHow It Works8-6. Inserting Results from a Stored ProcedureProblemSolutionHow It Works8-7. Inserting Multiple Rows at Once from Supplied ValuesProblemSolutionHow It Works8-8. Inserting Rows and Returning the Inserted RowsProblemSolutionHow It Works8-9. Updating a Single Row or Set of RowsProblemSolutionHow It Works8-10. Updating While Using a Second Table as the Data SourceProblemSolutionHow It Works8-11. Updating Data and Returning the Affected RowsProblemSolutionHow It Works8-12. Updating Large-Value ColumnsProblemSolutionHow It Works8-13. Deleting RowsProblemSolutionHow It Works8-14. Deleting Rows and Returning the Deleted RowsProblemSolutionHow It Works8-15. Deleting All Rows Quickly (Truncating)ProblemSolutionHow It Works8-16. Merging Data (Inserting, Updating, and/or Deleting Values)ProblemSolutionHow It Works8-17. Inserting Output DataProblemSolutionHow It Works
Chapter 9: Working with Strings
9-1. Concatenating Multiple StringsProblem/+SolutionHow It Works9-2. Finding a Character’s ASCII ValueProblemSolutionHow It Works9-3. Returning Integer and Character Unicode ValuesProblemSolutionHow It Works9-4. Locating Characters in a StringProblemSolutionHow It Works9-5. Determining the Similarity of StringsProblemSolutionHow It Works9-6. Returning the Leftmost or Rightmost Portion of a StringProblemSolutionHow It Works9-7. Returning Part of a StringProblemSolutionHow It Works9-8. Counting Characters or Bytes in a StringProblemSolutionHow It Works9-9. Replacing Part of a StringProblemSolutionHow It Works9-10. Stuffing a String into a StringProblemSolutionHow It Works9-11. Changing Between Lowercase and UppercaseProblemSolutionHow It Works9-12. Removing Leading and Trailing BlanksProblemSolutionHow It Works9-13. Repeating an Expression N TimesProblemSolutionHow It Works9-14. Repeating a Blank Space N TimesProblemSolutionHow It Works9-15. Reversing the Order of Characters in a StringProblemSolutionHow It Works
Chapter 10: Working with Dates and Times
10-1. Returning the Current Date and TimeProblemSolutionHow It Works10-2. Converting Between Time ZonesProblemSolutionHow It Works10-3. Converting a Date/Time Value to a Datetimeoffset ValueProblemSolutionHow It Works10-4. Incrementing or Decrementing a Date’s ValueProblemSolutionHow It Works10-5. Finding the Difference Between Two DatesProblemSolutionHow It Works10-6. Finding the Elapsed Time Between Two DatesProblemSolutionHow It Works10-7. Displaying the String Value for Part of a DateProblemSolutionHow It Works10-8. Displaying the Integer Representations for Parts of a DateProblemSolutionHow It Works10-9. Determining Whether a String Is a Valid DateProblemSolutionHow It Works10-10. Determining the Last Day of the MonthProblemSolutionHow It Works10-11. Creating a Date from NumbersProblemSolutionHow It Works10-12. Finding the Beginning Date of a DatepartProblemSolution #1Solution #2Solution #3How It Works #1How It Works #2How It Works #310-13. Include Missing DatesProblemSolutionHow It Works10-14. Finding Arbitrary DatesProblemSolutionHow It Works10-15. Querying for IntervalsProblemSolutionHow It Works10-16. Working with Dates and Times Across National BoundariesProblemSolutionHow It Works
Chapter 11: Working with Numbers
11-1. Representing IntegersProblemSolutionHow It Works11-2. Creating Single-Bit IntegersProblemSolutionHow It Works11-3. Representing Decimal and Monetary AmountsProblemSolutionHow It Works11-4. Representing Floating-Point ValuesProblemSolutionHow It Works11-5. Writing Mathematical ExpressionsProblemSolutionHow It Works11-6. Casting Between Data TypesProblemSolutionHow It Works11-7. Converting Numbers to TextProblemSolutionHow It Works11-8. Converting from Text to a NumberProblemSolutionHow It Works11-9. RoundingProblemSolutionHow It Works11-10. Rounding Always Up or DownProblemSolutionHow It Works11-11. Discarding Decimal PlacesProblemSolutionHow It Works11-12. Testing Equality of Binary Floating-Point ValuesProblemSolutionHow It Works11-13. Treating Nulls as ZerosProblemSolutionHow It Works11-14. Generating a Row Set of Sequential NumbersProblemSolutionHow It Works11-15. Generating Random Integers in a Row SetProblemSolutionHow It Works11-16. Reducing Space Used by Decimal StorageProblemSolutionHow It Works
Chapter 12: Transactions, Locking, Blocking, and Deadlocking
Transaction Control12-1. Using Explicit TransactionsProblemSolutionHow It Works12-2. Displaying the Oldest Active TransactionProblemSolutionHow It Works12-3. Querying Transaction Information by SessionProblemSolutionHow It WorksLocking12-4. Viewing Lock ActivityProblemSolutionHow It Works12-5. Controlling a Table’s Lock-Escalation BehaviorProblemSolutionHow It WorksTransaction, Locking, and Concurrency12-6. Configuring a Session’s Transaction-Locking BehaviorProblemSolutionHow It WorksBlocking12-7. Identifying and Resolving Blocking IssuesProblemSolutionHow It Works12-8. Configuring How Long a Statement Will Wait for a Lock to Be ReleasedProblemSolutionHow It WorksDeadlocking12-9. Identifying Deadlocks with a Trace FlagProblemSolutionHow It Works12-10. Identifying Deadlocks with Extended EventsProblemSolutionHow It Works12-11. Setting Deadlock PriorityProblemSolutionHow It Works
Chapter 13: Managing Tables
13-1. Creating a TableProblemSolutionHow It Works13-2. Adding a ColumnProblemSolutionHow It Works13-3. Adding a Column that Requires DataProblemSolutionHow It Works13-4. Changing a ColumnProblemSolutionHow It Works13-5. Creating a Computed ColumnProblemSolutionHow It Works13-6. Removing a ColumnProblemSolutionHow It Works13-7. Removing a TableProblemSolutionHow It Works13-8. Reporting on a Table’s DefinitionProblemSolutionHow It Works13-9. Reducing Storage Used by NULL ColumnsProblemSolutionHow It Works13-10. Adding a Constraint to a TableProblemSolutionHow It Works13-11. Creating a Recursive Foreign KeyProblemSolutionHow It Works13-12. Allowing Data Modifications to Foreign Key Columns in the Referenced Table to Be Reflected in the Referencing TableProblemSolutionHow It Works13-13. Specifying Default Values for a ColumnProblemSolutionHow It Works13-14. Validating Data as It Is Entered into a ColumnProblemSolutionHow It Works13-15. Temporarily Turning Off a ConstraintProblemSolutionHow It Works13-16. Removing a ConstraintProblemSolutionHow It Works13-17. Creating Auto-incrementing ColumnsProblemSolutionHow It Works13-18. Obtaining the Identity Value UsedProblemSolutionHow It Works13-19. Viewing or Changing the Seed Settings on an Identity ColumnProblemSolutionHow It Works13-20. Inserting Values into an Identity ColumnProblemSolutionHow It Works13-21. Automatically Inserting Unique ValuesProblemSolutionHow It Works13-22. Using Unique Identifiers Across Multiple TablesProblemSolutionHow It Works13-23. Using Temporary StorageProblemSolution #1Solution #2How It Works
Chapter 14: Managing Views
Regular Views14-1. Creating a ViewProblemSolutionHow It Works14-2. Querying a View’s DefinitionProblemSolutionHow It Works14-3. Obtaining a List of All Views in a DatabaseProblemSolutionHow It Works14-4. Obtaining a List of All Columns in a ViewProblemSolutionHow It Works14-5. Refreshing the Definition of a ViewProblemSolutionHow It Works14-6. Modifying a ViewProblemSolutionHow It Works14-7. Modifying Data Through a ViewProblemSolutionHow It Works14-8. Encrypting a ViewProblemSolutionHow It Works14-9. Indexing a ViewProblemSolutionHow It Works14-10. Creating a Partitioned ViewProblemSolutionHow It Works14-11. Creating a Distributed-Partitioned ViewProblemSolutionHow It Works
Chapter 15: Managing Large Tables and Databases
15-1. Partitioning a TableProblemSolutionHow It Works15-2. Locating Data in a PartitionProblemSolutionHow It Works15-3. Adding a PartitionProblemSolutionHow It Works15-4. Removing a PartitionProblemSolutionHow It Works15-5. Determining Whether a Table Is PartitionedProblemSolutionHow It Works15-6. Determining the Boundary Values for a Partitioned TableProblemSolutionHow It Works15-7. Determining the Partitioning Column for a Partitioned TableProblemSolutionHow It Works15-8. Determining the NEXT USED PartitionProblemSolutionHow It Works15-9. Moving a Partition to a Different Partitioned TableProblemSolutionHow It Works15-10. Moving Data from a Nonpartitioned Table to a Partition in a Partitioned TableProblemSolutionHow It Works15-11. Moving a Partition from a Partitioned Table to a Nonpartitioned TableProblemSolutionHow It Works15-12. Reducing Table Locks on Partitioned TablesProblemSolutionHow It Works15-13. Removing Partition Functions and SchemesProblemSolutionHow It Works15-14. Easing VLDB Manageability (with Filegroups)ProblemSolutionHow It Works15-15. Compressing Table DataProblemSolutionHow It Works15-16. Rebuilding a HeapProblemSolutionHow It Works
Chapter 16: Managing Indexes
Index Overview16-1. Creating a Table IndexProblemSolutionHow It Works16-2. Creating a Table IndexProblemSolution #1How It WorksSolution #2How It Works16-3. Enforcing Uniqueness on Non-key ColumnsProblemSolutionHow It Works16-4. Creating an Index on Multiple ColumnsProblemSolutionHow It Works16-5. Defining Index Column Sort DirectionProblemSolutionHow It Works16-6. Viewing Index MetadataProblemSolutionHow It Works16-7. Disabling an IndexProblemSolutionHow It Works16-8. Dropping IndexesProblemSolutionHow It Works16-9. Changing an Existing IndexProblemSolutionHow It WorksControlling Index Build Performance and Concurrency16-10. Sorting in TempdbProblemSolutionHow It Works16-11. Controlling Index Creation ParallelismProblemSolutionHow It Works16-12. User Table Access During Index CreationProblemSolutionHow It WorksIndex Options16-13. Using an Index INCLUDEProblemSolutionHow It Works16-14. Using PADINDEX and FILLFACTORProblemSolutionHow It Works16-15. Disabling Page and/or Row Index LockingProblemSolutionHow It WorksManaging Very Large Indexes16-16. Creating an Index on a FilegroupProblemSolutionHow It Works16-17. Implementing Index PartitioningProblemSolutionHow It Works16-18. Indexing a Subset of RowsProblemSolutionHow It Works16-19. Reducing Index SizeProblemSolutionHow It Works16-20. Further Reducing Index SizeProblemSolutionHow It Works
Chapter 17: Stored Procedures
17-1. Creating a Stored ProcedureProblemSolutionHow It Works17-2. Passing ParametersProblemSolutionHow It Works17-3. Making Parameters OptionalProblemSolutionHow It Works17-4. Making Early Parameters OptionalProblemSolutionHow It Works17-5. Returning OutputProblemSolutionHow It Works17-6. Modifying a Stored ProcedureProblemSolutionHow It Works17-7. Removing a Stored ProcedureProblemSolutionHow It Works17-8. Automatically Run a Stored Procedure at Start-UpProblemSolutionHow It Works17-9. Viewing a Stored Procedure’s DefinitionProblemSolutionHow It Works17-10. Documenting Stored ProceduresProblemSolutionHow It Works17-11. Determining the Current Nesting LevelProblemSolutionHow It Works17-12. Encrypting a Stored ProcedureProblemSolutionHow It Works17-13. Specifying a Security ContextProblemSolutionHow It Works17-14. Avoiding Cached Query PlansProblemSolutionHow It Works17-15. Flushing the Procedure CacheProblemSolutionHow It Works
Chapter 18: User-Defined Functions and Types
UDF Basics18-1. Creating Scalar FunctionsProblemSolutionHow It Works18-2. Creating Inline FunctionsProblemSolutionHow It Works18-3. Creating Multi-Statement User-Defined FunctionsProblemSolutionHow It Works18-4. Modifying User-Defined FunctionsProblemSolutionHow It Works18-5. Viewing UDF MetadataProblemSolutionHow It WorksBenefitting from UDFs18-6. Maintaining Reusable CodeProblemSolutionHow It Works18-7. Cross-Referencing Natural Key ValuesProblemSolutionHow It Works18-8. Replacing a View with a FunctionProblemSolutionHow It Works18-9. Dropping a FunctionProblemSolutionHow It WorksUDT Basics18-10. Creating and Using User-Defined TypesProblemSolutionHow It Works18-11. Identifying Dependencies on User-Defined TypesProblemSolutionHow It Works18-12. Passing Table-Valued ParametersProblemSolutionHow It Works18-13. Dropping User-Defined TypesProblemSolutionHow It Works
Chapter 19: In-Memory OLTP
19-1. Configuring a Database So That It Can Utilize In-Memory OLTPProblemSolution #1Solution #2How It Works19-2. Making a Memory-Optimized TableProblemSolutionHow It Works19-3. Creating a Memory-Optimized Table VariableProblemSolutionHow It Works19-4. Creating a Natively Compiled Stored ProcedureProblemSolutionHow It Works19-5. Determining Which Database Objects Are Configured to Use In-Memory OLTPProblemSolutionHow It Works19-6. Determining Which Objects Are Actively Using In-Memory OLTP on the ServerProblemSolutionHow It Works19-7. Detecting Performance Issues with Natively Compiled Stored Procedure ParametersProblemSolutionHow It Works19-8. Viewing CFP MetadataProblemSolutionHow It Works19-9. Disabling or Enabling Automatic MergingProblemSolutionHow It Works19-10. Manually Merging Checkpoint File PairsProblemSolutionHow It Works
Chapter 20: Triggers
20-1. Creating an AFTER DML TriggerProblemSolutionHow It Works20-2. Creating an INSTEAD OF DML TriggerProblemSolutionHow It Works20-3. Handling Transactions in TriggersProblemSolutionHow It Works20-4. Linking Trigger Execution to Modified ColumnsProblemSolutionHow It Works20-5. Viewing DML Trigger MetadataProblemSolutionHow It Works20-6. Creating a DDL TriggerProblemSolutionHow It Works20-7. Creating a Logon TriggerProblemSolutionHow It Works20-8. Viewing DDL Trigger MetadataProblemSolutionHow It Works20-9. Modifying a TriggerProblemSolutionHow It Works20-10. Enabling and Disabling a TriggerProblemSolutionHow It Works20-11. Nesting TriggersProblemSolutionHow It Works20-12. Controlling RecursionProblemSolutionHow It Works20-13. Specifying the Firing OrderProblemSolutionHow It Works20-14. Dropping a TriggerProblemSolutionHow It Works
Chapter 21: Error Handling
21-1. Handling Batch ErrorsProblemSolutionHow It Works21-2. What Are the Error Numbers and Messages Within SQL?ProblemSolutionHow It Works21-3. How Can I Implement Structured Error Handling in My Queries?ProblemSolutionHow It Works21-4. How Can I Use Structured Error Handling, but Still Return an Error?ProblemSolutionHow It Works21-5. Nested Error HandlingProblemSolutionHow It Works21-6. Throwing an ErrorProblemSolution #1: Use RAISERROR to throw an errorHow It WorksSolution #2: Use THROW to throw an errorHow It Works21-7. Creating a User-Defined ErrorProblemSolutionHow It Works21-8. Removing a User-Defined ErrorProblemSolutionHow It Works
Chapter 22: Query Performance Tuning
Query Performance TipsCapturing and Evaluating Query Performance22-1. Capturing Executing QueriesProblemSolution #1How It WorksSolution #2How It Works22-2. Viewing Estimated Query Execution PlansProblemSolutionHow It Works22-3. Viewing Execution Runtime InformationProblemSolutionHow It Works22-4. Viewing Statistics for Cached PlansProblemSolutionHow It Works22-5. Viewing Record Counts for Cached PlansProblemSolutionHow It Works22-6. Viewing Aggregated Performance Statistics Based on Query or Plan PatternsProblemSolutionHow It Works22-7. Identifying the Top BottleneckProblemSolutionHow It Works22-8. Identifying I/O Contention by Database and FileProblemSolutionHow It WorksMiscellaneous Techniques22-9. Parameterizing Ad Hoc QueriesProblemSolutionHow It Works22-10. Forcing the Use of a Query PlanProblemSolutionHow It Works22-11. Applying Hints Without Modifying a SQL StatementProblemSolutionHow It Works22-12. Creating Plan Guides from CacheProblemSolutionHow It Works22-13. Checking the Validity of a Plan GuideProblemSolutionHow It Works22-14. Parameterizing a Nonparameterized Query Using Plan GuidesProblemSolutionHow It Works22-15. Limiting Competing Query Resource ConsumptionProblemSolutionHow It Works
Chapter 23: Hints
23-1. Forcing a Join’s Execution ApproachProblemSolutionHow It Works23-2. Forcing a Statement RecompileProblemSolutionHow It Works23-3. Executing a Query Without LockingProblemSolution #1: The NOLOCK HintSolution #2: The Isolation LevelHow It Works23-4. Forcing an Index SeekProblemSolutionHow It Works23-5. Forcing an Index ScanProblemSolutionHow It Works23-6. Optimizing for First RowsProblemSolutionHow It Works23-7. Specifying Join OrderProblemSolutionHow It Works23-8. Forcing the Use of a Specific IndexProblemSolutionHow It Works23-9. Optimizing for Specific Parameter ValuesProblemSolutionHow It Works
Chapter 24: Index Tuning and Statistics
Index TuningIndex Maintenance24-1. Displaying Index FragmentationProblemSolutionHow It Works24-2. Rebuilding IndexesProblemSolutionHow It Works24-3. Defragmenting IndexesProblemSolutionHow It Works24-4. Rebuilding a HeapProblemSolutionHow It Works24-5. Displaying Index UsageProblemSolutionHow It WorksStatistics24-6. Manually Creating StatisticsProblemSolutionHow It Works24-7. Creating Statistics on a Subset of RowsProblemSolutionHow It Works24-8. Updating StatisticsProblemSolutionHow It Works24-9. Generating Statistics Across All TablesProblemSolutionHow It Works24-10. Updating Statistics Across All TablesProblemSolutionHow It Works24-11. Viewing Statistics DetailsProblemSolutionHow It Works24-12. Removing StatisticsProblemSolutionHow It Works24-13. Finding When Stats Need to Be CreatedProblemSolutionHow It Works
Chapter 25: XML
25-1. Creating an XML ColumnProblemSolutionHow It Works25-2. Inserting XML DataProblemSolutionHow It Works25-3. Validating XML DataProblemSolutionHow It Works25-4. Verifying the Existence of XML Schema CollectionsProblemSolutionHow It Works25-5. Retrieving XML DataProblemSolutionHow It Works25-6. Modifying XML DataProblemSolutionHow It Works25-7. Indexing XML DataProblemSolutionHow It Works25-8. Formatting Relational Data as XMLProblemSolutionHow It Works25-9. Formatting XML Data as RelationalProblemSolutionHow It Works25-10. Using XML to Return a Delimited StringProblemSolutionHow It Works
Chapter 26: Files, Filegroups, and Integrity
26-1. Adding a Data File or a Log FileProblemSolutionHow It Works26-2. Retrieving Information about the Files in a DatabaseProblemSolutionHow It Works26-3. Removing a Data File or a Log FileProblemSolutionHow It Works26-4. Relocating a Data File or a Log FileProblemSolutionHow It Works26-5. Changing a File’s Logical NameProblemSolutionHow It Works26-6. Increasing the Size of a Database FileProblemSolutionHow It Works26-7. Adding a FilegroupProblemSolutionHow It Works26-8. Adding a File to a FilegroupProblemSolutionHow It Works26-9. Setting the Default FilegroupProblemSolutionHow It Works26-10. Adding Data to a Specific FilegroupProblemSolutionHow It Works26-11. Moving Data to a Different FilegroupProblemSolution #1Solution #2Solution #3How It Works26-12. Removing a FilegroupProblemSolutionHow It Works26-13. Making a Database or a Filegroup Read-OnlyProblem #1Problem #2Solution #1Solution #2How It Works26-14. Viewing Database Space UsageProblemSolution #1Solution #2Solution #3Solution #4How It Works26-15. Shrinking the Database or a Database FileProblemSolution #1Solution #2How It Works26-16. Checking the Consistency of Allocation StructuresProblemSolutionHow It Works26-17. Checking Allocation and Structural IntegrityProblemSolutionHow It Works26-18. Checking the Integrity of Tables in a FilegroupProblemSolutionHow It Works26-19. Checking the Integrity of Specific Tables and Indexed ViewsProblemSolution #1Solution #2Solution #3How It Works26-20. Checking Constraint IntegrityProblemSolutionHow It Works26-21. Checking System Table ConsistencyProblemSolutionHow It Works
Chapter 27: Backup
27-1. Backing Up a DatabaseProblemSolutionHow It Works27-2. Compressing a BackupProblemSolutionHow It Works27-3. Ensuring That a Backup Can Be RestoredProblemSolutionHow It Works27-4. Transaction Log BackupProblemSolutionHow It Works27-5. Understanding Why the Transaction Log Continues to GrowProblemSolutionHow It Works27-6. Performing a Differential BackupProblemSolutionHow It Works27-7. Backing Up a Single Row or TableProblemSolutionHow It Works27-8. Creating a Database SnapshotProblemSolutionHow It Works27-9. Backing Up Data Files or FilegroupsProblemSolution #1: Perform a File BackupSolution #2: Perform a Filegroup BackupHow It Works27-10. Mirroring Backup FilesProblemSolutionHow It Works27-11. Backing Up a Database Without Affecting the Normal Sequence of BackupsProblemSolutionHow It Works27-12. Querying Backup DataProblemSolutionHow It Works27-13. Encrypting a BackupProblemSolutionHow It Works27-14. Compressing an Encrypted BackupProblemSolutionHow It Works27-15. Backing Up CertificatesProblemSolutionHow It Works27-16. Backing Up to AzureProblemSolutionHow It Works27-17. Backing Up to Multiple FilesProblemSolutionHow It Works
Chapter 28: Recovery
28-1. Restoring a Database from a Full BackupProblemSolutionHow It Works28-2. Restoring a Database from a Transaction Log BackupProblemSolutionHow It Works28-3. Restoring a Database from a Differential BackupProblemSolutionHow It Works28-4. Restoring a File or FilegroupProblemSolutionHow It Works28-5. Performing a Piecemeal (PARTIAL) RestoreProblemSolutionHow It Works28-6. Restoring a PageProblemSolutionHow It Works28-7. Identifying Databases with Multiple Recovery PathsProblemSolutionHow It Works28-8. Restore a Single Row or TableProblemSolution #1: Restore Rows from a BackupHow It WorksSolution #2: Restore Rows from a Database SnapshotHow It Works28-9. Recover from a Backup in Azure Blob StorageProblemSolutionHow It Works28-10. Recover a CertificateProblemSolutionHow It Works
Chapter 29: Principals and Users
Windows Principals29-1. Creating a Windows LoginProblemSolutionHow It Works29-2. Viewing Windows LoginsProblemSolutionHow It Works29-3. Altering a Windows LoginProblemSolutionHow It Works29-4. Dropping a Windows LoginProblemSolutionHow It Works29-5. Denying SQL Server Access to a Windows User or GroupProblemSolutionHow It WorksSQL Server Principals29-6. Creating a SQL Server LoginProblemSolutionHow It Works29-7. Viewing SQL Server LoginsProblemSolutionHow It Works29-8. Altering a SQL Server LoginProblemSolutionHow It Works29-9. Managing a Login’s PasswordProblemSolutionHow It Works29-10. Dropping a SQL LoginProblemSolutionHow It Works29-11. Managing Server Role MembersProblemSolutionHow It Works29-12. Reporting Fixed Server Role InformationProblemSolutionHow It WorksDatabase Principals29-13. Creating Database UsersProblemSolutionHow It Works29-14. Reporting Database User InformationProblemSolutionHow It Works29-15. Modifying a Database UserProblemSolutionHow It Works29-16. Removing a Database User from the DatabaseProblemSolutionHow It Works29-17. Fixing Orphaned Database UsersProblemSolutionHow It Works29-18. Reporting Fixed Database Roles InformationProblemSolutionHow It Works29-19. Managing Fixed Database Role MembershipProblemSolutionHow It Works29-20. Managing User-Defined Database RolesProblemSolutionHow It Works29-21. Managing Application RolesProblemSolutionHow It Works29-22. Managing User-Defined Server RolesProblemSolutionHow It Works
Chapter 30: Securables, Permissions, and Auditing
Permissions Overview30-1. Reporting SQL Server Assignable PermissionsProblemSolutionHow It WorksServer-Scoped Securables and Permissions30-2. Managing Server PermissionsProblemSolutionHow It Works30-3. Querying Server-Level PermissionsProblemSolutionHow It WorksDatabase-Scoped Securables and Permissions30-4. Managing Database PermissionsProblemSolutionHow It Works30-5. Querying Database PermissionsProblemSolutionHow It WorksSchema-Scoped Securables and Permissions30-6. Managing SchemasProblemSolutionHow It Works30-7. Managing Schema PermissionsProblemSolutionHow It WorksObject Permissions30-8. Managing Object PermissionsProblemSolutionHow It WorksManaging Permissions Across Securable Scopes30-9. Determining Permissions to a SecurableProblemSolutionHow It Works30-10. Reporting Permissions by Securable ScopeProblemSolutionHow It Works30-11. Changing Securable OwnershipProblemSolutionHow It Works30-12. Allowing Access to Non-SQL Server ResourcesProblemSolutionHow It WorksAuditing Activity of Principals Against Securables30-13. Defining Audit Data SourcesProblemSolutionHow It Works30-14. Capturing SQL Instance–Scoped EventsProblemSolutionHow It Works30-15. Capturing Database-Scoped EventsProblemSolutionHow It Works30-16. Querying Captured Audit DataProblemSolutionHow It Works30-17. Managing, Modifying, and Removing Audit ObjectsProblemSolutionHow It Works
Chapter 31: Objects and Dependencies
31-1. Changing the Name of Database ItemsProblemSolutionHow It Works31-2. Changing an Object’s SchemaProblemSolutionHow It Works31-3. Identifying Object DependenciesProblemSolutionHow It Works31-4. Identifying Referencing and Referenced EntitiesProblemSolutionHow It Works31-5. Viewing the Definition of Coded ObjectsProblemSolution #1Solution #2How It Works31-6. Returning a Database Object’s Name, Schema Name, and Object IDProblemSolution #1Solution #2How It Works
Index

Content preview from SQL Server T-SQL Recipes, Fourth Edition

CHAPTER 18

User-Defined Functions and Types

by Jason Brimhall

In this chapter, I’ll present recipes for user-defined functions and types. User-defined functions (UDFs) allow you to encapsulate both logic and subroutines into a single function that can then be used within your Transact-SQL queries and programmatic objects. User-defined types (UDTs) allow you to create an alias type based on an underlying system data type and enforce a specific data type, length, and nullability.

In this chapter, I’ll also cover the SQL Server user-defined table type, which can be used as a user-defined table parameter for passing table result sets within your ...

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

Publisher Resources

ISBN: 9781484200612Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Server T-SQL Recipes, Fourth Edition

by Jason Brimhall, Jonathan Gennick, Wayne Sheffield

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.