SQL Server T-SQL Recipes, 4th Edition.pdf

(5868 KB) Pobierz
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
About the Authors�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½lxxiii
About the Technical Reviewer �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½lxxv
Acknowledgments �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½lxxvii
Introduction �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½lxxix
Chapter 1: Getting Started with SELECT �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
1
Chapter 2: Elementary Programming �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
27
Chapter 3: Working with NULLS�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
51
Chapter 4: Querying from Multiple Tables �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
67
Chapter 5: Aggregations and Grouping �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
91
Chapter 6: Advanced Select Techniques�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
115
Chapter 7: Windowing Functions�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
141
Chapter 8: Inserting, Updating, Deleting�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
173
Chapter 9: Working with Strings �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
213
Chapter 10: Working with Dates and Times �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
233
Chapter 11: Working with Numbers �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
255
Chapter 12: Transactions, Locking, Blocking, and Deadlocking �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
279
Chapter 13: Managing Tables �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
313
Chapter 14: Managing Views�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
347
Chapter 15: Managing Large Tables and Databases�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
367
Chapter 16: Managing Indexes �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
389
v
Contents at a GlanCe
Chapter 17: Stored Procedures �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
417
Chapter 18: User-Defined Functions and Types �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
437
Chapter 19: In-Memory OLTP �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
473
Chapter 20: Triggers �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
495
Chapter 21: Error Handling �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
531
Chapter 22: Query Performance Tuning �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
551
Chapter 23: Hints �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
599
Chapter 24: Index Tuning and Statistics �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
613
Chapter 25: XML �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
639
Chapter 26: Files, Filegroups, and Integrity �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
663
Chapter 27: Backup �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
703
Chapter 28: Recovery �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
733
Chapter 29: Principals and Users �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
761
Chapter 30: Securables, Permissions, and Auditing �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
799
Chapter 31: Objects and Dependencies �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
859
Index �½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½�½
873
vi
Introduction
Sometimes all one wants is a good example.
T-SQL is fundamental to working with SQL Server. Almost everything you do, from querying a table to
creating indexes to backing up and recovering, ultimately comes down to T-SQL statements being issued and
executed. Sometimes a utility executes statements on your behalf; other times you must write them yourself.
And when you have to write them yourself, you’re probably going to be in a hurry. Information
technology is like that. It’s a field full of stress and deadlines, and don’t we all just want to get home for
dinner with our families?
We sure do want to be home for dinner, and that brings us full circle to the example-based format
you’ll find in this book. If you have a job to do that’s covered in this book, you can count on a clear code
example and very few words to waste your time. We put the code first! And explain it afterward. We hope
our examples are clear enough that you can just crib from them and get on with your day, but the detailed
explanations are there if you need them.
We’ve missed a few dinners from working on this book. We hope it helps you avoid the same fate.
Who This Book Is For
SQL Server T-SQL Recipes is aimed at developers deploying applications against Microsoft SQL Server 2012
and 2014. The book also helps database administrators responsible for managing those databases. Any
developer or administrator valuing good code examples will find something of use in this book.
Conventions
Throughout the book, we’ve tried to keep to a consistent style for presenting SQL and results. Where a piece
of code, a SQL reserved word, or a fragment of SQL is presented in the text, it is presented in fixed-width
Courier font, such as this example:
SELECT * FROM HumanResources.Employee;
Where we discuss the syntax and options of SQL commands, we use a conversational style so you can
quickly reach an understanding of the command or technique. We have chosen not to duplicate complex
syntax diagrams that are best left to the official, vendor-supplied documentation. Instead, we take an
example-based approach that is easy to understand and adapt.
Downloading the Code
The code for the examples shown in this book is available on the Apress web site,
www.apress.com.
A link can
be found on the book’s information page (www.apress.com/9781484200629) on the Source Code/Downloads
tab. This tab is located in the Related Titles section of the page.
lxxix
Zgłoś jeśli naruszono regulamin