Get a detailed look at the internal architecture of T-SQL with this comprehensive programming reference. Database developers and administrators get best practices, expert techniques, and code samples to master the intricacies of the programming language—solving complex problems with real-world solutions.
Discover how to:
Understand when to apply set-based programming techniques
Design and implement date and time-related XML and CLR datatypes
Use temporary objects, including temporary tables, table variables, and table expressions
Work with T-SQL and CLR user-defined functions, stored procedures, and triggers
Support user input-based queries and variable data with dynamic execution
Work with transactions and new exception handling constructs
Apply a concurrency model to support simultaneous users
Use Service Broker for controlled asynchronous processing in database applications
This book includes code samples on the Web.
Chapter 1 Datatype-Related Problems, XML, and CLR UDTs
DATETIME Datatypes
Character-Related Problems
Large Objects
Implicit Conversions
CLR-Based User-Defined Types
XML Data Type
Conclusion
Chapter 2 Temporary Tables and Table Variables
Temporary Tables
Table Variables
tempdb Considerations
Table Expressions
Comparison Summary
Summary Exercise—Relational Division
Conclusion
Chapter 3 Cursors
Using Cursors
Cursor Overhead
Dealing with Each Row Individually
Order-Based Access
Conclusion
Chapter 4 Dynamic SQL
EXEC
sp_executesql
Environmental Settings
Uses of Dynamic SQL
SQL Injection
Conclusion
Chapter 5 Views
What Are Views?
ORDER BY in a View
Refreshing Views
Modular Approach
Updating Views
View Options
Indexed Views
Conclusion
Chapter 6 User-Defined Functions
Some Facts About UDFs
Scalar UDFs
Table-Valued UDFs
Per-Row UDFs
Conclusion
Chapter 7 Stored Procedures
Types of Stored Procedures
The Stored Procedure Interface
Resolution
Compilations, Recompilations, and Reuse of Execution Plans
EXECUTE AS
Parameterizing Sort Order
Dynamic Pivot
CLR Stored Procedures
Conclusion
Chapter 8 Triggers
AFTER Triggers
INSTEAD OF Triggers
DDL Triggers
CLR Triggers
Conclusion
Chapter 9 Transactions
What Are Transactions?
Locks
Isolation Levels
Save Points
Deadlocks
Conclusion
Chapter 10 Exception Handling
Exception Handling Prior to SQL Server 2005
Exception Handling in SQL Server 2005
Conclusion
Chapter 11 Service Broker
Dialog Conversations
Sample Dialog
Dialog Security
Routing and Distribution
Scenarios
Where Does Service Broker Fit?
Conclusion
Appendix A Companion to CLR Routines
Create the CLRUtilities Database: SQL Server
Development: Visual Studio
Deployment and Testing: Visual Studio and SQL Server
Itzik Ben-Gan is a mentor and cofounder of Solid Quality Mentors. A Microsoft MVP for SQL Server since 1999, Ben-Gan teaches and consults internationally on T-SQL querying, programming, and query tuning. He is the coauthor of Inside Microsoft SQL Server: T-SQL Querying and Inside Microsoft SQL Server:T-SQL Programming. He has written numerous articles for SQL Server Magazine and MSDN®, and speaks at industry events such as Microsoft Tech*Ed, DevWeek, PASS, and SQL Server Connections.
Lubor Kollar is group program manager with the SQL Server Customer Advisory Team at Microsoft, working on the most challenging SQL Server deployments worldwide. He has 13+ years of experience with SQL Server development.
Dejan Sarka
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.
