Inside Microsoft® SQL Server™ 2005

Inside Microsoft® SQL Server™ 2005

by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka
Released May 2006
Publisher(s): Microsoft Press
ISBN: 9780735621978

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

A comprehensive, hands-on reference for database developers and administrators, this book focuses on advanced language features of Transact-SQL (T-SQL), the database programming language built into SQL Server. Includes code and table examples.

Table of contents

  1. Inside Microsoft® SQL Server™ 2005: T-SQL Programming
  2. A Note Regarding Supplemental Files
  3. Foreword
  4. Preface
  5. Acknowledgments
  6. Introduction
    1. Organization of This Book
    2. System Requirements
    3. Installing Sample Databases
    4. Updates
    5. Code Samples
    6. Support for This Book
  7. 1. Datatype-Related Problems, XML, and CLR UDTs
    1. DATETIME Datatypes
      1. Storage Format of DATETIME
      2. Datetime Manipulation
        1. Literals
        2. Rounding Issues
        3. DATETIME Functions
        4. No Separation Between Date and Time
      3. Datetime-Related Querying Problems
        1. The Birthday Problem
        2. Overlaps
          1. Identifying Overlaps
          2. Grouping Overlaps
          3. Maximum Number of Overlapping Sessions (Set-Based Solution)
        3. Identifying Weekday
        4. Grouping by the Week
        5. ISO Week
        6. Working Days
        7. Generating a Series of Dates
    2. Character-Related Problems
      1. Pattern Matching
      2. Case-Sensitive Filters
    3. Large Objects
      1. MAX Specifier
      2. BULK Rowset Provider
    4. Implicit Conversions
      1. Scalar Expressions
      2. Filter Expressions
    5. CLR-Based User-Defined Types
      1. Theoretical Introduction to UDTs
        1. Domains and Relations
        2. Relations and Classes
        3. Domains and Classes
        4. Complex Domains
        5. Why Do We Need Complex Classes?
        6. Language for Creating UDTs
      2. Programming a UDT
        1. UDT Requirements
        2. Creating a UDT
        3. Deploying the UDT Using T-SQL
    6. XML Data Type
      1. XML Support in a Relational Database
        1. Is the Relational Model Obsolete?
      2. When Should You Use XML Instead of Relational Representation?
      3. XML Serialized Objects in a Database
      4. Using XML with Open Schema
      5. XML Data Type as a Parameter of a Stored Procedure
      6. XQuery Modification Statements
    7. Conclusion
  8. 2. Temporary Tables and Table Variables
    1. Temporary Tables
      1. Local Temporary Tables
        1. tempdb
        2. Scope and Visibility
        3. Transaction Context
        4. Statistics
        5. Temporary Table Name Resolution
        6. Schema Changes to Temporary Tables in Dynamic Batches
      2. Global Temporary Tables
    2. Table Variables
      1. Limitations
      2. tempdb
      3. Scope and Visibility
      4. Transaction Context
      5. Statistics
    3. tempdb Considerations
    4. Table Expressions
    5. Comparison Summary
    6. Summary Exercise—Relational Division
    7. Conclusion
  9. 3. Cursors
    1. Using Cursors
    2. Cursor Overhead
    3. Dealing with Each Row Individually
    4. Order-Based Access
      1. Custom Aggregates
      2. Running Aggregations
      3. Maximum Concurrent Sessions
      4. Matching Problems
    5. Conclusion
  10. 4. Dynamic SQL
    1. EXEC
      1. A Simple EXEC Example
      2. EXEC Has No Interface
      3. Concatenating Variables
      4. EXEC AT
    2. sp_executesql
      1. The sp_executesql Interface
      2. Statement Limit
    3. Environmental Settings
    4. Uses of Dynamic SQL
      1. Dynamic Maintenance Activities
      2. Storing Computations
      3. Dynamic Filters
      4. Dynamic PIVOT/UNPIVOT
    5. SQL Injection
      1. SQL Injection: Code Constructed Dynamically at Client
      2. SQL Injection: Code Constructed Dynamically at Server
      3. Protecting Against SQL Injection
    6. Conclusion
  11. 5. Views
    1. What Are Views?
    2. ORDER BY in a View
    3. Refreshing Views
    4. Modular Approach
    5. Updating Views
    6. View Options
      1. ENCRYPTION
      2. SCHEMABINDING
      3. CHECK OPTION
      4. VIEW_METADATA
    7. Indexed Views
    8. Conclusion
  12. 6. User-Defined Functions
    1. Some Facts About UDFs
    2. Scalar UDFs
      1. T-SQL Scalar UDFs
      2. Performance Issues
      3. UDFs Used in Constraints
        1. DEFAULT Constraints
        2. CHECK Constraints
        3. PRIMARY KEY and UNIQUE Constraints
      4. CLR Scalar UDFs
        1. CLR Routines
        2. Regular Expressions
        3. Explicit vs. Implicit Conversions
      5. SQL Signature
        1. T-SQL SQL Signature UDF
        2. CLR SQL Signature UDF
        3. Compare Performance of T-SQL and CLR SQL Signature UDFs
    3. Table-Valued UDFs
      1. Inline Table-Valued UDFs
      2. Split Array
        1. T-SQL Split UDF
        2. CLR Split UDF
        3. Compare Performance of T-SQL and CLR Split
      3. Multistatement Table-Valued UDFs
    4. Per-Row UDFs
    5. Conclusion
  13. 7. Stored Procedures
    1. Types of Stored Procedures
      1. User-Defined Stored Procedures
      2. Special Stored Procedures
      3. System Stored Procedures
      4. Other Types of Stored Procedures
    2. The Stored Procedure Interface
      1. Input Parameters
      2. Output Parameters
    3. Resolution
    4. Compilations, Recompilations, and Reuse of Execution Plans
      1. Reuse of Execution Plans
      2. Recompilations
      3. Parameter Sniffing Problem
    5. EXECUTE AS
    6. Parameterizing Sort Order
    7. Dynamic Pivot
    8. CLR Stored Procedures
    9. Conclusion
  14. 8. Triggers
    1. AFTER Triggers
      1. The inserted and deleted Special Tables
      2. Identifying the Number of Affected Rows
      3. Identifying the Type of Trigger
      4. Not Firing Triggers for Specific Statements
      5. Nesting and Recursion
      6. UPDATE and COLUMNS_UPDATED
      7. Auditing Example
    2. INSTEAD OF Triggers
      1. Per-Row Triggers
      2. Used with Views
      3. Automatic Handling of Sequences
    3. DDL Triggers
      1. Database-Level Triggers
      2. Server-Level Triggers
    4. CLR Triggers
    5. Conclusion
  15. 9. Transactions
    1. What Are Transactions?
    2. Locks
    3. Isolation Levels
      1. Read Uncommitted
      2. Read Committed
      3. Repeatable Read
      4. Serializable
      5. New Isolation Levels
        1. Snapshot
          1. Conflict Detection
        2. Read Committed Snapshot
    4. Save Points
    5. Deadlocks
      1. Simple Deadlock Example
      2. Deadlock Caused by Missing Indexes
      3. Deadlock with a Single Table
    6. Conclusion
  16. 10. Exception Handling
    1. Exception Handling Prior to SQL Server 2005
    2. Exception Handling in SQL Server 2005
      1. TRY/CATCH
      2. New Exception-Handling Functions
      3. Errors in Transactions
        1. Using XACT_STATE
        2. Using Save Points
        3. Deadlocks and Update Conflicts
    3. Conclusion
  17. 11. Service Broker
    1. Dialog Conversations
      1. Conversations
        1. Reliable
        2. In Order
        3. Asynchronous
      2. Messages
      3. Contracts
      4. DEFAULT
      5. Queues
      6. Services
      7. Begining and Ending Dialogs
      8. Conversation Endpoints
      9. Conversation Groups
      10. Sending and Receiving
    2. Sample Dialog
      1. Poison Messages
    3. Dialog Security
      1. Asymmetric Key Authentication
      2. Configuring Dialog Security
    4. Routing and Distribution
      1. Adjacent Broker Protocol
      2. Service Broker Endpoints
        1. Configuring Adjacent Broker Protocol Connections
          1. Windows Authentication
          2. Certificate Authentication
      3. Routes
        1. Broker Instance
        2. Incoming Routes
        3. Wildcards and Special Addresses
        4. Load Balancing
        5. Routes and Database Mirroring
        6. Forwarding
    5. Scenarios
      1. Reliable SOA
      2. Asynchronous Processing
    6. Where Does Service Broker Fit?
      1. What Service Broker Is
      2. What Service Broker Isn’t
      3. Service Broker and MSMQ
      4. Service Broker and BizTalk
      5. Service Broker and Windows Communication Foundation
    7. Conclusion
  18. A. Companion to CLR Routines
    1. Create the CLRUtilities Database: SQL Server
    2. Development: Visual Studio
      1. 1. Create Project
      2. 2. Develop Code
    3. Deployment and Testing: Visual Studio and SQL Server
      1. 3. Deploy and Build Solution
      2. 4. Test Solution
  19. About the Contributors
    1. Itzik Ben-Gan
    2. Dejan Sarka
    3. Roger Wolter
    4. Steve Kass
    5. Lubor Kollar
  20. Index
  21. About the Authors
  22. Copyright

Product information

  • Title: Inside Microsoft® SQL Server™ 2005
  • Author(s): Itzik Ben-Gan, Lubor Kollar, Dejan Sarka
  • Release date: May 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621978