SQL Server 2008 Transact-SQL Recipes (eBook)

Joseph Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail, and the real estate industry. Prior to joining SQLskills, he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting, and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development, and high-availability. In 2006 Joe earned the Microsoft Certified Master: SQL Server 2005 certification; and in 2008, he earned the Microsoft Certified Master: SQL Server 2008 certification. In 2009 he took over responsibility for the SQL Server Microsoft Certified Master program and held that post until 2011. He is the author of a few books and white papers, including most recently, SQL Server 2008 Transact-SQL Recipes (Apress, 2008). Joe's blog is at www.SQLskills.com/blogs/joe, and he can be reached at joe@SQLskills.com.

Contents at a Glance 4
Contents 6
About the Author 24
About the Technical Reviewer 25
Acknowledgments 26
Introduction 27
SELECT 28
The Basic SELECT Statement 28
Selective Querying Using a Basic WHERE Clause 30
Using Operators and Expressions 34
Grouping Data 41
Ordering Results 44
SELECT Clause Techniques 48
Subqueries 54
Querying from More Than One Data Source 55
Using APPLY to Invoke a Table-Valued Function for Each Row 62
Advanced Techniques for Data Sources 65
Summarizing Data 73
Common Table Expressions 83
Perform, Capture, and Track Data Modifications 90
INSERT 90
UPDATE 100
DELETE 113
Advanced Data Modification Techniques 116
Capturing and Tracking Data Modification Changes 120
Transactions, Locking, Blocking, and Deadlocking 141
Transaction Control 141
Locking 148
Transaction, Locking, and Concurrency 154
Blocking 160
Deadlocking 163
Tables 169
Table Basics 169
Collation Basics 181
Keys 183
Surrogate Keys 191
Constraints 194
Temporary Tables and Table Variables 202
Manageability for Very Large Tables 206
Indexes 222
Index Overview 222
Controlling Index Build Performance and Concurrency 232
Index Options 234
Managing Very Large Indexes 237
Full-Text Search 241
Full-Text Indexes and Catalogs 241
Basic Searching 254
Advanced Searching 256
Ranked Searching 259
Views 262
Regular Views 263
View Encryption 270
Indexed Views 271
Partitioned Views 274
SQL Server Functions 280
Aggregate Functions 280
Mathematical Functions 284
String Functions 286
Working with NULLs 297
Date Functions 300
Type Conversion 307
Ranking Functions 311
Probing Server, Database, and Connection-Level Settings Using System Functions 316
IDENTITY and uniqueidentifier Functions 326
Conditional Processing, Control-of- Flow, and Cursors 330
Conditional Processing 330
Control-of-Flow 335
Cursors 342
Stored Procedures 347
Stored Procedure Basics 347
Stored Procedure Security 357
Recompilation and Caching 362
User-Defined Functions and Types 365
UDF Basics 365
Benefitting from UDFs 379
UDT Basics 387
Triggers 395
DML Triggers 396
DDL Triggers 408
Managing Triggers 415
CLR Integration 423
CLR Overview 424
When (and When Not) to Use Assemblies 424
CLR Objects Overview 426
Creating CLR Database Objects 426
Administering Assemblies 439
XML, Hierarchies, and Spatial Data 441
Working with Native XML 441
Converting Between XML Documents and Relational Data 452
Working with Native Hierarchical Data 457
Native Spatial Data 464
Hints 471
Using Join Hints 471
Using Query Hints 473
Using Table Hints 476
Error Handling 481
System-Defined and User-Defined Error Messages 481
Manually Raising an Error 484
Trapping and Handling Application Errors 487
Principals 496
Windows Principals 496
SQL Server Principals 501
Database Principals 509
Securables, Permissions, and Auditing 521
Permissions Overview 522
Server-Scoped Securables and Permissions 525
Database-Scoped Securables and Permissions 529
Schema-Scoped Securables and Permissions 534
Object Permissions 539
Managing Permissions Across Securable Scopes 542
Auditing SQL Instance and Database-Level Activity of Principals Against Securables 549
Encryption 566
Encryption by Passphrase 566
Master Keys 569
Asymmetric Key Encryption 574
Symmetric Key Encryption 579
Certificate Encryption 586
Transparent Data Encryption 594
Service Broker 598
Example Scenario: Online Bookstore 599
Creating a Basic Service Broker Application 599
Creating a Stored Procedure to Process Messages 617
Remote-Server Service Broker Implementations 620
Event Notifications 631
Configuring and Viewing SQL Server Options 634
Creating and Configuring Databases 639
Creating, Altering, and Dropping Databases 639
Configuring Database Options 653
Controlling Database Access and Ownership 669
Managing Database Files and Filegroups 672
Viewing and Managing Database Space Usage 681
Database Integrity and Optimization 687
Database Integrity Checking 687
Tables and Constraints 692
Index Maintenance 700
Maintaining Database Objects and Object Dependencies 705
Database Object Maintenance 705
Object Dependencies 708
Database Mirroring 714
Database Mirroring in Context 714
Database Mirroring Architecture 715
Setting Up Database Mirroring 717
Setup Summary 726
Operating Database Mirroring 727
Monitoring and Configuring Options 731
Database Snapshots 734
Snapshot Basics 734
Linked Servers and Distributed Queries 740
Linked Server Basics 740
Linked Server Logins 745
Executing Distributed Queries 747
Query Performance Tuning 756
Query Performance Tips 757
Capturing and Evaluating Query Performance 759
Index Tuning 771
Statistics 777
Miscellaneous Techniques 783
Backup and Recovery 805
Creating a Backup and Recovery Plan 805
Making Backups 807
Restoring a Database 826
Index 839