You are previewing Pro T-SQL 2008 Programmer's Guide.
O'Reilly logo
Pro T-SQL 2008 Programmer's Guide

Book Description

Pro T-SQL 2005 Programmer's Guide provides comprehensive and detailed coverage of all the major features and facilities of T-SQL for SQL Server 2005. It is designed for all users of T-SQL, database administrators, systems administrators, application developers, and end-users, who want to learn how to exploit the power of T-SQL. Whatever you need to do with T-SQL, you'll find it described clearly here.

Stored procedures, triggers, and user-defined functions are only part of the story. Dynamic SQL, the new XML data type, and SQLCLR programming are also covered, as well as accessing SQL Server with ADO.NET. Exception handling and performance are fully covered, too. This book is truly a complete programmer's guide to T-SQL and SQL Server 2005.

Table of Contents

  1. About the Author
  2. About the Technical Reviewer
  3. Acknowledgments
  4. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Chapter 1
    4. Chapter 2
    5. Chapter 3
    6. Chapter 4
    7. Chapter 5
    8. Chapter 6
    9. Chapter 7
    10. Chapter 8
    11. Chapter 9
    12. Chapter 10
    13. Chapter 11
    14. Chapter 12
    15. Chapter 13
    16. Chapter 14
    17. Chapter 15
    18. Chapter 16
    19. Chapter 17
    20. Chapter 18
    21. Chapter 19
    22. Appendix A
    23. Appendix B
    24. Appendix C
    25. Appendix D
    26. Conventions
    27. Prerequisites
    28. Downloading the Code
    29. Contacting the Author
  5. 1. Foundations of T-SQL
    1. A Short History of T-SQL
    2. Imperative vs. Declarative Languages
    3. SQL Basics
      1. Statements
      2. Databases
      3. Transaction Logs
      4. Schemas
      5. Tables
      6. Views
      7. Indexes
      8. Stored Procedures
      9. User-Defined Functions
      10. SQL CLR Assemblies
    4. Elements of Style
      1. Whitespace
      2. Naming Conventions
      3. One Entry, One Exit
      4. Defensive Coding
      5. SQL-92 Syntax Outer Joins
      6. The SELECT * Statement
      7. Variable Initialization
    5. Summary
  6. 2. T-SQL 2008 New Features
    1. Productivity Enhancements
    2. The MERGE Statement
    3. New Data Types
      1. Date and Time Data Types
      2. The hierarchyid Data Type
      3. hierarchyid Methods
      4. Spatial Data Types
    4. Grouping Sets
    5. Other New Features
    6. Summary
  7. 3. Tools of the Trade
    1. SQL Server Management Studio
      1. SSMS Editing Options
      2. Context-Sensitive Help
      3. Graphical Query Execution Plans
      4. Project Management Features
      5. The Object Explorer
    2. The SQLCMD Utility
    3. Business Intelligence Development Studio
    4. SQL Profiler
    5. SQL Server Integration Services
    6. The Bulk Copy Program
    7. SQL Server 2008 Books Online
    8. The AdventureWorks Sample Database
    9. Summary
  8. 4. Procedural Code and CASE Expressions
    1. Three-Valued Logic
    2. Control-of-Flow Statements
      1. The BEGIN and END Keywords
      2. The IF...ELSE Statement
      3. The WHILE, BREAK, and CONTINUE Statements
      4. The GOTO Statement
      5. The WAITFOR Statement
      6. The RETURN Statement
      7. The TRY...CATCH Statement
    3. The CASE Expression
      1. The Simple CASE Expression
      2. The Searched CASE Expression
      3. CASE and Pivot Tables
      4. COALESCE and NULLIF
    4. Cursors
    5. Summary
  9. 5. User-Defined Functions
    1. Scalar Functions
      1. Recursion in Scalar User-Defined Functions
      2. Procedural Code in User-Defined Functions
    2. Multistatement Table-Valued Functions
    3. Inline Table-Valued Functions
    4. Restrictions on User-Defined Functions
      1. Nondeterministic Functions
      2. State of the Database
    5. Summary
  10. 6. Stored Procedures
    1. Introducing Stored Procedures
    2. Calling Stored Procedures
    3. Managing Stored Procedures
    4. Stored Procedures in Action
    5. Recursion in Stored Procedures
    6. Table-Valued Parameters
    7. Temporary Stored Procedures
    8. Recompilation and Caching
      1. Stored Procedure Statistics
      2. Parameter Sniffing
      3. Recompilation
    9. Summary
  11. 7. Triggers
    1. DML Triggers
      1. When to Use DML Triggers
      2. Auditing with DML Triggers
      3. Nested and Recursive Triggers
      4. The UPDATE and COLUMNS_UPDATED Functions
      5. Triggers on Views
    2. DDL Triggers
    3. Logon Triggers
    4. Summary
  12. 8. Encryption
    1. The Encryption Hierarchy
    2. Service Master Keys
    3. Database Master Keys
    4. Certificates
    5. Asymmetric Keys
    6. Symmetric Keys
    7. Encryption Without Keys
    8. Hashing Data
    9. Extensible Key Management
    10. Transparent Data Encryption
    11. Summary
  13. 9. Common Table Expressions and Windowing Functions
    1. Common Table Expressions
      1. Multiple Common Table Expressions
      2. Recursive Common Table Expressions
    2. Windowing Functions
      1. The ROW_NUMBER Function
      2. The RANK and DENSE_RANK Functions
      3. The NTILE Function
      4. Aggregate Functions and OVER
    3. Summary
  14. 10. Integrated Full-Text Search
    1. iFTS Architecture
    2. Creating Full-Text Catalogs and Indexes
      1. Creating Full-Text Catalogs
      2. Creating Full-Text Indexes
    3. Full-Text Querying
      1. The FREETEXT Predicate
      2. The CONTAINS Predicate
      3. The FREETEXTTABLE and CONTAINSTABLE Functions
    4. Thesauruses and Stoplists
    5. Stored Procedures and Dynamic Management Views and Functions
    6. Summary
  15. 11. XML
    1. Legacy XML
      1. OPENXML
      2. OPENXML Result Formats
    2. FOR XML Clause
      1. FOR XML RAW
      2. FOR XML AUTO
      3. FOR XML EXPLICIT
      4. FOR XML PATH
    3. The xml Data Type
      1. Untyped xml
      2. Typed xml
    4. The xml Data Type Methods
      1. The query Method
      2. The value Method
      3. The exist Method
      4. The nodes Method
      5. The modify Method
    5. XML Indexes
    6. XSL Transformations
    7. Summary
  16. 12. XQuery and XPath
    1. XPath and FOR XML PATH
      1. XPath Attributes
      2. Columns Without Names and Wildcards
      3. Element Grouping
      4. The data Function
      5. XPath and NULL
      6. The WITH XMLNAMESPACES Clause
      7. Node Tests
    2. XQuery and the xml Data Type
      1. Expressions and Sequences
      2. The query Method
      3. Location Paths
      4. Node Tests
      5. Namespaces
      6. Axis Specifiers
      7. Dynamic XML Construction
      8. XQuery Comments
      9. Data Types
      10. Predicates
      11. Conditional Expressions (if...then...else)
      12. Arithmetic Expressions
      13. XQuery Functions
      14. Constructors and Casting
      15. FLWOR Expressions
    3. Summary
  17. 13. Catalog Views and Dynamic Management Views
    1. Catalog Views
      1. Table and Column Metadata
      2. Index Metadata
      3. Querying Permissions
    2. Dynamic Management Views and Functions
      1. Session Information
      2. Connection Information
      3. Currently Executing SQL
      4. Tempdb Space
      5. Server Resources
      6. Unused Indexes
    3. INFORMATION_SCHEMA Views
    4. Summary
  18. 14. SQL CLR Programming
    1. The Old Way
    2. The SQL CLR Way
    3. SQL CLR Assemblies
    4. User-Defined Functions
    5. Stored Procedures
    6. User-Defined Aggregates
      1. Creating a Simple UDA
      2. Creating an Advanced UDA
    7. SQL CLR User-Defined Types
    8. Summary
  19. 15. .NET Client Programming
    1. ADO.NET
    2. The .NET SQL Client
      1. Connected Data Access
      2. Disconnected Datasets
    3. Parameterized Queries
    4. Nonquery, Scalar, and XML Querying
    5. SqlBulkCopy
    6. Multiple Active Result Sets
    7. LINQ to SQL
      1. Using the O/RM Designer
      2. Querying with LINQ to SQL
      3. Inspecting the O/RM Classes
    8. Summary
  20. 16. Data Services
    1. Introducing HTTP Endpoints
      1. Consuming HTTP Endpoints
    2. Web Services
    3. ADO.NET Data Services
      1. Creating an ADO.NET Data Service
      2. Creating an ADO.NET Data Service Consumer
    4. Summary
  21. 17. New T-SQL Features
    1. Set Operators
    2. The OUTPUT Clause
    3. The TOP Keyword
    4. CROSS APPLY and OUTER APPLY
    5. The TABLESAMPLE Clause
    6. The NEWSEQUENTIALID Function
    7. Date and Time Functionse
    8. The max Data Types
    9. Synonyms
    10. FILESTREAM Support
      1. Enabling FILESTREAM Support
      2. Creating FILESTREAM Filegroups
      3. FILESTREAM-Enabling Tables
      4. Accessing FILESTREAM Data
    11. Summary
  22. 18. Error Handling and Dynamic SQL
    1. Error Handling
      1. Legacy Error Handling
      2. Try...Catch Exception Handling
      3. The RAISERROR Statement
    2. Debugging Tools
      1. PRINT Statement Debugging
      2. Trace Flags
      3. SSMS Integrated Debugger
      4. Visual Studio T-SQL Debugger
    3. Dynamic SQL
      1. The EXECUTE Statement
      2. SQL Injection and Dynamic SQL
      3. Troubleshooting Dynamic SQL
    4. The sp_executesql Stored Procedure
      1. Dynamic SQL and Scope
      2. Client-Side Parameterization
    5. Summary
  23. 19. Performance Tuning
    1. SQL Server Storage
      1. Files and Filegroups
      2. Space Allocation
      3. Data Compression
    2. Indexes
      1. Heaps
      2. Clustered Indexes
      3. Nonclustered Indexes
      4. Filtered Indexes
    3. Optimizing Queries
      1. Reading Query Plans
      2. Methodology
    4. Summary
  24. A. Exercise Answers
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Chapter 10
    11. Chapter 11
    12. Chapter 12
    13. Chapter 13
    14. Chapter 14
    15. Chapter 15
    16. Chapter 16
    17. Chapter 17
    18. Chapter 18
    19. Chapter 19
  25. B. XQuery Data Types
  26. C. Glossary
  27. D. SQLCMD Quick Reference
    1. Command-Line Options
    2. Scripting Variables
    3. Commands