Inside Microsoft SQL Server 2005 T-SQL Programming

by Sarka, Dejan; Ben-Gan, Itzik

• ISBN13: 9780735621978
• ISBN10: 0735621977
• Edition: 1st
• Format: Paperback
• Copyright: 2006-05-17
• Publisher: Microsoft Press

Summary

This thorough, hands-on reference for database developers and administrators delivers expert guidance on sophisticated uses of Transact-SQL (T-SQL)--one of the most familiar and powerful programming languages for SQL Server. Written by a T-SQL guru, this guide focuses on language features and how they are interpreted and processed by the SQL Server execution engine. You'll get in-depth coverage of the sophisticated uses of T-SQL, including triggers, user-defined functions, exception handling, and more. The book explains and compares solutions to database-development problems in both SQL Server 2000 and SQL Server 2005, discussing the new T-SQL programming features added to SQL Server 2005 in detail. Includes extensive code samples, table examples, and logic puzzles to help database developers and administrators understand the intricacies and help promote mastery of T-SQL.

Author Biography

Dejan Sarka is MCT and MCDBA certified and a Microsoft MVP for SQL Server. He teaches and consults for Solid Quality Mentors, speaks at TechEd and PASS, and develops OLTP, OLAP, and data mining solutions.

Table of Contents

Foreword	p. xi
Preface	p. xiii
Acknowledgments	p. xvii
Introduction	p. xxi
Datatype-Related Problems, XML, and CLR UDTs	p. 1
DATETIME Datatypes	p. 2
Storage Format of DATETIME	p. 2
Datetime Manipulation	p. 3
Datetime-Related Querying Problems	p. 8
Character-Related Problems	p. 25
Pattern Matching	p. 26
Case-Sensitive Filters	p. 31
Large Objects	p. 32
MAX Specifier	p. 32
BULK Rowset Provider	p. 34
Implicit Conversions	p. 36
Scalar Expressions	p. 36
Filter Expressions	p. 37
CLR-Based User-Defined Types	p. 40
Theoretical Introduction to UDTs	p. 41
Programming a UDT	p. 48
XML Data Type	p. 65
XML Support in a Relational Database	p. 65
When Should You Use XML Instead of Relational Representation?	p. 67
XML Serialized Objects in a Database	p. 68
Using XML with Open Schema	p. 75
XML Data Type as a Parameter of a Stored Procedure	p. 81
XQuery Modification Statements	p. 82
Conclusion	p. 83
Temporary Tables and Table Variables	p. 85
Temporary Tables	p. 86
Local Temporary Tables	p. 86
Global Temporary Tables	p. 94
Table Variables	p. 96
Limitations	p. 96
tempdb	p. 97
Scope and Visibility	p. 97
Transaction Context	p. 97
Statistics	p. 98
tempdb Considerations	p. 101
Table Expressions	p. 102
Comparison Summary	p. 103
Summary Exercise-Relational Division	p. 104
Conclusion	p. 109
Cursors	p. 111
Using Cursors	p. 112
Cursor Overhead	p. 114
Dealing with Each Row Individually	p. 115
Order-Based Access	p. 116
Custom Aggregates	p. 116
Running Aggregations	p. 118
Maximum Concurrent Sessions	p. 122
Matching Problems	p. 131
Conclusion	p. 138
Dynamic SQL	p. 139
EXEC	p. 141
A Simple EXEC Example	p. 141
EXEC Has No Interface	p. 142
Concatenating Variables	p. 145
EXEC AT	p. 146
sp_executesql	p. 149
The sp_executesql Interface	p. 149
Statement Limit	p. 152
Environmental Settings	p. 153
Uses of Dynamic SQL	p. 153
Dynamic Maintenance Activities	p. 153
Storing Computations	p. 156
Dynamic Filters	p. 160
Dynamic PIVOT/UNPIVOT	p. 166
SQL Injection	p. 172
SQL Injection: Code Constructed Dynamically at Client	p. 172
SQL Injection: Code Constructed Dynamically at Server	p. 173
Protecting Against SQL Injection	p. 177
Conclusion	p. 179
Views	p. 181
What Are Views?	p. 181
ORDER BY in a View	p. 183
Refreshing Views	p. 187
Modular Approach	p. 189
Updating Views	p. 198
View Options	p. 202
ENCRYPTION	p. 202
SCHEMABINDING	p. 203
CHECK OPTION	p. 204
VIEW_METADATA	p. 205
Indexed Views	p. 206
Conclusion	p. 211
User-Defined Functions	p. 213
Some Facts About UDFs	p. 214
Scalar UDFs	p. 214
T-SQL Scalar UDFs	p. 215
Performance Issues	p. 217
UDFs Used in Constraints	p. 219
CLR Scalar UDFs	p. 222
SQL Signature	p. 231
Table-Valued UDFs	p. 239
Inline Table-Valued UDFs	p. 239
Split Array	p. 242
Multistatement Table-Valued UDFs	p. 248
Per-Row UDFs	p. 252
Conclusion	p. 255
Stored Procedures	p. 257
Types of Stored Procedures	p. 258
User-Defined Stored Procedures	p. 258
Special Stored Procedures	p. 262
System Stored Procedures	p. 264
Other Types of Stored Procedures	p. 266
The Stored Procedure Interface	p. 267
Input Parameters	p. 267
Output Parameters	p. 269
Resolution	p. 273
Compilations, Recompilations, and Reuse of Execution Plans	p. 275
Reuse of Execution Plans	p. 275
Recompilations	p. 281
Parameter Sniffing Problem	p. 284
EXECUTE AS	p. 288
Parameterizing Sort Order	p. 289
Dynamic Pivot	p. 294
CLR Stored Procedures	p. 305
Conclusion	p. 313
Triggers	p. 315
AFTER Triggers	p. 316
The inserted and deleted Special Tables	p. 316
Identifying the Number of Affected Rows	p. 318
Identifying the Type of Trigger	p. 321
Not Firing Triggers for Specific Statements	p. 324
Nesting and Recursion	p. 328
UPDATE and COLUMNS_UPDATED	p. 329
Auditing Example	p. 333
INSTEAD OF Triggers	p. 335
Per-Row Triggers	p. 336
Used with Views	p. 339
Automatic Handling of Sequences	p. 342
DDL Triggers	p. 344
Database-Level Triggers	p. 346
Server-Level Triggers	p. 350
CLR Triggers	p. 351
Conclusion	p. 360
Transactions	p. 361
What Are Transactions?	p. 362
Locks	p. 364
Isolation Levels	p. 370
Read Uncommitted	p. 371
Read Committed	p. 372
Repeatable Read	p. 373
Serializable	p. 374
New Isolation Levels	p. 375
Save Points	p. 381
Deadlocks	p. 383
Simple Deadlock Example	p. 384
Deadlock Caused by Missing Indexes	p. 385
Deadlock with a Single Table	p. 388
Conclusion	p. 390
Exception Handling	p. 391
Exception Handling prior to SQL Server 2005	p. 391
Exception Handling in SQL Server 2005	p. 395
TRY/CATCH	p. 395
New Exception-Handling Functions	p. 396
Errors in Transactions	p. 399
Conclusion	p. 409
Service Broker	p. 411
Dialog Conversations	p. 411
Conversations	p. 412
Messages	p. 415
Contracts	p. 417
DEFAULT	p. 418
Queues	p. 418
Services	p. 423
Begining and Ending Dialogs	p. 424
Conversation Endpoints	p. 426
Conversation Groups	p. 428
Sending and Receiving	p. 430
Sample Dialog	p. 434
Poison Messages	p. 442
Dialog Security	p. 443
Asymmetric Key Authentication	p. 444
Configuring Dialog Security	p. 445
Routing and Distribution	p. 448
Adjacent Broker Protocol	p. 449
Service Broker Endpoints	p. 450
Routes	p. 455
Scenarios	p. 460
Reliable SOA	p. 460
Asynchronous Processing	p. 461
Where Does Service Broker Fit?	p. 462
What Service Broker Is	p. 462
What Service Broker Isn't	p. 462
Service Broker and MSMQ	p. 462
Service Broker and BizTalk	p. 463
Service Broker and Windows Communication Foundation	p. 463
Conclusion	p. 464
Companion to CLR Routines	p. 465
Create the CLRUtilities Database: SQL Server	p. 466
Development: Visual Studio	p. 466
Deployment and Testing: Visual Studio and SQL Server	p. 467
Index	p. 491
Table of Contents provided by Ingram. All Rights Reserved.