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 this programming language—solving complex problems with real-world solutions.
Discover how to:
Work with T-SQL and CLR user-defined functions, stored procedures, and triggers.
Handle transactions, concurrency, and error handling.
Efficiently use temporary objects, including temporary tables, table variables, and table expressions.
Evaluate when to use set-based programming techniques and when to use cursors.
Work with dynamic SQL in an efficient and secure manner.
Treat date- and time-related data in a robust manner.
Develop CLR user-defined types and learn about temporal support in the relational model.
Use XML and XQuery and implement a dynamic schema solution.
Work with spatial data using the new geometry and geography types and spatial indexes.
Track access and changes to data using extended events, SQL Server Audit, change tracking, and change data capture.
Use Service Broker for controlled asynchronous processing in database applications.
All the book’s code samples will be available for download from the companion Web site.
Table of Contents
- 1. Views
2. User-Defined Functions
- Some Facts About UDFs
- T-SQL Scalar UDFs
- Performance Issues
- UDFs Used in Constraints
- CLR Scalar UDFs
- SQL Signature
- Table-Valued UDFs
- Per-Row UDFs
3. Stored Procedures
- Types of Stored Procedures
- The Stored Procedure Interface
- Dependency Information
- Compilations, Recompilations, and Reuse of Execution Plans
- EXECUTE AS
- Parameterizing Sort Order
- CLR Stored Procedures
- The inserted and deleted Special Tables
- Identifying the Number of Affected Rows
- Identifying the Type of Trigger
- Not Firing Triggers for Specific Statements
- Nesting and Recursion
- UPDATE and COLUMNS_UPDATED
- Auditing Example
- INSTEAD OF Triggers
- Per-Row Triggers
- Used with Views
- Automatic Handling of Sequences
- DDL Triggers
- Logon Triggers
- CLR Triggers
- AFTER Triggers
5. Transactions and Concurrency
- What Are Transactions?
- Locking and Blocking
- Lock Escalation
- Isolation Levels
- 6. Error Handling
7. Temporary Tables and Table Variables
- Temporary Tables
- Table Variables
- tempdb Considerations
- Table Expressions
- Comparison Summary
- Summary Exercises
- 8. Cursors
9. Dynamic SQL
- Environmental Settings
Uses of Dynamic SQL
- Dynamic Maintenance Activities
- Storing Computations
- Dynamic Filters
- Dynamic PIVOT/UNPIVOT
- SQL Injection
10. Working with Date and Time
- Date and Time Data Types
Date and Time Manipulation
- Date and Time Functions
- Identifying Weekday
- Handling Date-only or Time-only Data Prior to SQL Server 2008
- Examples of Date and Time Calculations
- Rounding Issues
- Date- and Time-Related Querying Problems
- 11. CLR User-Defined Types
12. Temporal Support in the Relational Model
- Timestamped Predicates and Propositions
- Semitemporal Problems
- Tables with Full Temporal Support
- Unpack and Pack
- Sixth Normal Form in Use
13. XML and XQuery
- Converting Relational Data to XML and Vice Versa
- The XQuery Language in SQL Server 2008
XML Data Type
- XML Support in a Relational Database
- When Should You Use XML Instead of Relational Representation?
- XML Serialized Objects in a Database
- XML as a Stored Procedure Parameter
- Dynamic Relational Schema
14. Spatial Data
- Introduction to Spatial Data
- Basic Spatial Data Concepts
Getting Started with Spatial Data
- Creating a Table with a Spatial Column
- Well-Known Text
- Constructing Spatial Objects from Strings and Inserting into a Table
- Basic Object Interaction Tests
- Basic Spatial Operations
- Proximity Queries
- The GEOGRAPHY Type
- Spatial Data Validity
- Measuring Length and Area
- Indexing Spatial Data
Using Spatial Data to Solve Problems
- Loading Spatial Data
- Loading Spatial Data from Text Files
- Finding Site Locations within Geographic Regions
- Nearest Neighbor Searches
- Spatial Joins
- Processing Spatial Data
- Extending Spatial Support with CLR Routines
15. Tracking Access and Changes to Data
- Which Technology Do I Use?
- Extended Events Implementation
- SQL Server Audit Implementation
- Change Tracking Implementation
- Change Data Capture Implementation
16. Service Broker
- Dialog Conversations
- Conversation Priority
- Sample Dialog
- Poison Messages
- Dialog Security
- Routing and Distribution
- Where Does Service Broker Fit?
- A. Companion to CLR Routines
- B. About the Authors
- About the Authors
- Title: Inside Microsoft® SQL Server® 2008: T-SQL Programming
- Release date: September 2009
- Publisher(s): Microsoft Press
- ISBN: 9780735639928