Pro T-SQL 2008 Programmer's Guide

Pro T-SQL 2008 Programmer's Guide

by Michael Coles
Released April 2008
Publisher(s): Apress
ISBN: 9781430210016

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Discussing new and existing features, SQL Server designer and administrator Michael Coles takes you on an expert guided tour of Transact-SQL functionality in SQL Server 2008 in his book, Pro T-SQL 2008 Programmer's Guide. Fully functioning examples and downloadable source code bring Coles' technically accurate and engaging treatment of Transact-SQL into your own hands. Step-by-step explanations ensure clarity, and an advocacy of best-practices will steer you down the road to success.

Pro T-SQL 2008 Programmer's Guide is every developer's key to making full use of SQL Server 2008's powerful, built-in Transact-SQL language. Transact-SQL is the language developers and DBAs use to interact with SQL Server. It's used for everything from querying data, to writing stored procedures, to managing the database. New features in SQL Server 2008 include a spatial data type, SQLCLR integration, the MERGE statement, a dramatically improved and market-leading XML feature set, and support for encryption—all of which are covered in this book

Table of contents

  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. B. XQuery Data Types
  27. C. Glossary
  28. D. SQLCMD Quick Reference
    1. Command-Line Options
    2. Scripting Variables
    3. Commands

Product information

  • Title: Pro T-SQL 2008 Programmer's Guide
  • Author(s): Michael Coles
  • Release date: April 2008
  • Publisher(s): Apress
  • ISBN: 9781430210016