Inside Microsoft® SQL Server® 2008: T-SQL Programming

Inside Microsoft® SQL Server® 2008: T-SQL Programming

Released September 2009
Publisher(s): Microsoft Press
ISBN: 9780735639928

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

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. Dedication
    2. Foreword
    3. Acknowledgments
    4. Introduction
      1. Hardware and Software Requirements
      2. Companion Content and Sample Database
      3. Find Additional Content Online
      4. Support for These Books
        1. Questions and Comments
    5. 1. 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
    6. 2. 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. String Manipulation
            1. Matching Based on Regular Expressions
            2. Replacement Based on Regular Expressions
            3. Formatting Date and Time Values
          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. ORDER Option for CLR Table-Valued UDFs
        4. Multistatement Table-Valued UDFs
      4. Per-Row UDFs
      5. Conclusion
    7. 3. 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. Scalar Input Parameters
        2. Table-Valued Parameters
        3. Output Parameters
      3. Resolution
      4. Dependency Information
      5. Compilations, Recompilations, and Reuse of Execution Plans
        1. Reuse of Execution Plans
        2. Recompilations
        3. Variable Sniffing
          1. OPTIMIZE FOR UNKNOWN
        4. Plan Guides
          1. Object Plan Guides
          2. SQL Plan Guides
          3. Template Plan Guides
          4. Using a Fixed XML Plan
          5. Plan Freezing
      6. EXECUTE AS
      7. Parameterizing Sort Order
      8. CLR Stored Procedures
      9. Conclusion
    8. 4. 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
        8. INSTEAD OF Triggers
        9. Per-Row Triggers
        10. Used with Views
        11. Automatic Handling of Sequences
      2. DDL Triggers
        1. Database-Level Triggers
        2. Server-Level Triggers
      3. Logon Triggers
      4. CLR Triggers
      5. Conclusion
    9. 5. Transactions and Concurrency
      1. What Are Transactions?
      2. Locking and Blocking
      3. Lock Escalation
      4. Isolation Levels
        1. Read Uncommitted
        2. Read Committed
        3. Repeatable Read
        4. Serializable
        5. Row Versioning–Based Isolation Levels
          1. Snapshot
            1. Conflict Detection
          2. Read Committed Snapshot
      5. Savepoints
      6. Deadlocks
        1. Simple Deadlock Example
        2. Deadlock Caused by Missing Indexes
        3. Deadlock with a Single Table
      7. Conclusion
    10. 6. Error Handling
      1. Error Handling without the TRY/CATCH Construct
      2. Error Handling with the TRY/CATCH Construct
        1. TRY/CATCH
        2. Error-Handling Functions
        3. Errors in Transactions
          1. Using XACT_STATE
          2. Using Savepoints
          3. Deadlocks and Update Conflicts
      3. Conclusion
    11. 7. 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
          7. Caching of Temporary Objects
        2. Global Temporary Tables
      2. Table Variables
        1. Limitations
        2. tempdb
        3. Scope and Visibility
        4. Transaction Context
        5. Statistics
        6. Minimally Logged Inserts
      3. tempdb Considerations
      4. Table Expressions
      5. Comparison Summary
      6. Summary Exercises
        1. Comparing Periods
        2. Recent Orders
        3. Relational Division
      7. Conclusion
    12. 8. 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
    13. 9. Dynamic SQL
      1. EXEC
        1. Simple EXEC Examples
        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
          1. Static Solution Using IS NULL
          2. Static Solution Using COALESCE
          3. Using the RECOMPILE Statement Option
          4. Using an Inline Table UDF
          5. Static Solution Based on Multiple Procedures
          6. Solution Based on Dynamic SQL
        4. Dynamic PIVOT/UNPIVOT
          1. Dynamic PIVOT
          2. Generalizing Dynamic Pivoting
          3. Dynamic 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
    14. 10. Working with Date and Time
      1. Date and Time Data Types
      2. Date and Time Manipulation
        1. Date and Time Functions
          1. Functions Supported Prior to SQL Server 2008
          2. Functions Introduced in SQL Server 2008
        2. Literals
        3. Identifying Weekday
        4. Handling Date-only or Time-only Data Prior to SQL Server 2008
        5. Examples of Date and Time Calculations
          1. First or Last Day of a Period
          2. Previous or Next Weekday
          3. First or Last Weekday
        6. Rounding Issues
      3. Date- and Time-Related Querying Problems
        1. Age Problems
        2. Overlaps
          1. Identifying Overlaps
          2. Grouping Overlaps
        3. Grouping by the Week
        4. Working Days
        5. Generating a Series of Dates
      4. Conclusion
    15. 11. CLR User-Defined Types
      1. Theoretical Introduction to UDTs
        1. Domains and Relations
        2. Domains and Classes
        3. Complex Domains
        4. Why Do We Need Complex Classes?
        5. Language for Creating UDTs
      2. Programming a UDT
        1. UDT Requirements
        2. Creating a UDT
        3. Deploying the UDT Using T-SQL
      3. Conclusion
    16. 12. Temporal Support in the Relational Model
      1. Timestamped Predicates and Propositions
        1. Time Points
        2. Time Points Lookup Table
      2. Semitemporal Problems
        1. Semitemporal Constraints
        2. Testing Semitemporal Constraints
        3. Queries on Semitemporal Tables
      3. Tables with Full Temporal Support
        1. The IntervalCID UDT
        2. Testing IntervalCID
        3. Full Temporal Tables Using IntervalCID
        4. Testing Full Temporal Constraints
        5. Queries on Tables with Full Temporal Support
      4. Unpack and Pack
        1. Expanded and Collapsed Forms of Interval Sets
        2. The UNPACK Operator
        3. PACK Operator
      5. Sixth Normal Form in Use
        1. Horizontal and Vertical Decompositions
        2. Sixth Normal Form
      6. Conclusion
    17. 13. XML and XQuery
      1. Converting Relational Data to XML and Vice Versa
        1. Introduction to XML
        2. Producing XML from Relational Data
          1. FOR XML RAW
          2. FOR XML AUTO
          3. FOR XML EXPLICIT
          4. FOR XML PATH
        3. Shredding XML to Tables
      2. The XQuery Language in SQL Server 2008
        1. XQuery Basics
          1. Data Types
          2. Functions
        2. Navigation
          1. Predicates
          2. Examples of Navigation
        3. Iteration and Returning Values
      3. 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
          1. The value and query XML Data Type Methods
          2. XML Indexes
          3. The modify and exists XML Data Type Methods
          4. The nodes XML Data Type Method
          5. Reading the Execution Plan
        4. XML as a Stored Procedure Parameter
      4. Dynamic Relational Schema
        1. Relational Solutions
        2. Object-Oriented Solutions
        3. Using the XML Data Type for Dynamic Schema
      5. Conclusion
    18. 14. Spatial Data
      1. Introduction to Spatial Data
      2. Basic Spatial Data Concepts
        1. Vector Data and the OGC Simple Features Type Model
        2. Planar and Geographic Coordinates
        3. Spatial Reference Identifiers
        4. Standards
        5. Working on the Ellipsoid
      3. Data
        1. Common Forms of Data
        2. Finding Spatial Data
        3. Loading Spatial Data
        4. Sample Spatial Data
      4. Getting Started with Spatial Data
        1. Creating a Table with a Spatial Column
        2. Well-Known Text
        3. Constructing Spatial Objects from Strings and Inserting into a Table
        4. Basic Object Interaction Tests
        5. Basic Spatial Operations
          1. Intersecting Spatial Objects
          2. Union of Spatial Objects
          3. Generalization of Spatial Objects
        6. Proximity Queries
          1. Distance between Spatial Objects
          2. Spatial Buffers
          3. Comparing Spatial Buffers and Distance-Based Calculations to Test Proximity
        7. The GEOGRAPHY Type
          1. The Geography Type and SRIDs
          2. The Geography Type and Coordinate Ordering
          3. Coordinate Systems and Units of Measure
          4. The Extent of Geography Objects
      5. Spatial Data Validity
        1. Data Validity Issues with Geometry Data
      6. Measuring Length and Area
        1. Comparing Length Measurements between GEOMETRY and GEOGRAPHY Instances
        2. Comparing Area Measurements between GEOMETRY and GEOMETRY Instances
      7. Indexing Spatial Data
        1. Spatial Index Basics
        2. SQL Server Spatial Indexes
        3. Using Spatial Indexes
        4. Geography Indexes
        5. Query Plans
        6. Integration with Spatial Methods
      8. Using Spatial Data to Solve Problems
        1. Loading Spatial Data
          1. Shape2SQL
          2. MapPoint 2009 Add-In for SQL Server
        2. Loading Spatial Data from Text Files
          1. Common Workflow Patterns
            1. Using the GEOMETRY MakeValid Method
            2. Forcing Polygon Ring Reorientation
          2. Moving Geometry Data to Geography Data
          3. Using MakeValidGeographyFromGeometry
        3. Finding Site Locations within Geographic Regions
          1. Find High Schools within 2 KM of Interstate 5 in King County, Washington State
          2. Find Schools Within 4 KM of the Intersection of Interstate 5 and Interstate 405 in King County, Washington State
        4. Nearest Neighbor Searches
          1. Find the Nearest GeoNames Data around a Point Location
          2. Find ZIP Codes around a Point Location
        5. Spatial Joins
        6. Processing Spatial Data
          1. Processing the Highways Table in the Sample_USA Database
          2. The Workflow
      9. Extending Spatial Support with CLR Routines
        1. Types on the Client
        2. User Defined Aggregate: Union and Dissolve
        3. Sinks and Builders: Linear Transformations
      10. Conclusion
    19. 15. Tracking Access and Changes to Data
      1. Which Technology Do I Use?
        1. Approaches Using Earlier SQL Server Versions
        2. Technologies Added in SQL Server 2008
          1. Extended Events
          2. SQL Server Audit
          3. Change Tracking
          4. Change Data Capture
      2. Extended Events Implementation
        1. Extended Events Object Hierarchy
          1. Modules
          2. Packages
          3. Events
          4. Predicates
          5. Actions
          6. Maps
          7. Targets
        2. Implementing a Scenario Using Extended Events
        3. Exploring Extended Events Concepts
          1. Asynchronous File Target
          2. Viewing Existing Sessions
          3. Managing Existing Event Sessions
      3. SQL Server Audit Implementation
        1. Auditing Object Hierarchy
          1. Audit
          2. Server Audit Specification
          3. Database Audit Specification
        2. Implementing a Scenario Using Auditing
        3. Exploring SQL Server Audit Concepts
          1. Viewing Details of Audits
          2. Indirect and Parameterized Access
          3. Auditing to the Security Event Log
          4. Management Issues for SQL Server Audit
      4. Change Tracking Implementation
        1. Implementing a Scenario Using Change Tracking
        2. Change Tracking Management Issues
        3. Microsoft Sync Framework
      5. Change Data Capture Implementation
        1. Implementing a Scenario Using Change Data Capture
        2. Change Data Capture Management Issues
          1. Schema Changes
          2. Performance
          3. Security
          4. Effects on the Source System
          5. Removing the CDC Database
      6. Conclusion
    20. 16. Service Broker
      1. Dialog Conversations
        1. Conversations
        2. Reliable
          1. Ordered
          2. Asynchronous
        3. Messages
          1. Contracts
        4. DEFAULT Message Type
        5. Queues
          1. Services
        6. Beginning and Ending Dialogs
        7. Conversation Endpoints
        8. Conversation Groups
        9. Sending and Receiving
      2. Activation
        1. Internal Activation
          1. Activation Procedures
          2. Activation Security Context
          3. Using Activation
        2. External Activation
          1. QUEUE_ACTIVATION Event
          2. External Activation Service
      3. Conversation Priority
        1. Broker Priority Object
          1. Examples
      4. Sample Dialog
      5. Poison Messages
      6. Dialog Security
        1. Asymmetric Key Authentication
        2. Configuring Dialog Security
      7. 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
      8. Troubleshooting
        1. Conversation Population Explosion
        2. No Reply
        3. Poison Message Queue Disable
        4. The ssbdiagnose Utility
      9. Scenarios
        1. Reliable SOA
        2. Asynchronous Processing
      10. 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
      11. Conclusion
    21. A. Companion to CLR Routines
      1. Create the CLRUtilities Database: SQL Server
      2. Development: Visual Studio
        1. Create a Project
        2. Develop Code
      3. Deployment and Testing: Visual Studio and SQL Server
        1. Build and Deploy the Solution
        2. Test the Solution
    22. B. About the Authors
    23. Index
    24. About the Authors
    25. Copyright

    Product information

    • Title: Inside Microsoft® SQL Server® 2008: T-SQL Programming
    • Author(s):
    • Release date: September 2009
    • Publisher(s): Microsoft Press
    • ISBN: 9780735639928