O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Defensive Database Programming with SQL Server

Book Description

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected. To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings. Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality of your T-SQL code and increase its resilience and robustness.

Table of Contents

  1. Copyright
  2. About the Author
  3. Author Acknowledgements
  4. About the Technical Reviewer
  5. Introduction
    1. What this book covers
      1. Ch. 01: Basic Defensive Database Programming Techniques
      2. Ch. 02: Code Vulnerabilities due to SQL Server Misconceptions
      3. Ch. 03: Surviving Changes to Database Objects
      4. Ch. 04: When Upgrading Breaks Code
      5. Ch. 05: Reusing T-SQL Code
      6. Ch. 06: Common Problems with Data Integrity
      7. Ch. 07: Advanced Use of Constraints
      8. Ch. 08: Defensive Error Handling
      9. Ch. 09: Surviving Concurrent Queries (Paid editions only)
      10. Ch. 10: Surviving Concurrent Modifications (Paid editions only)
    2. What this book does not cover
    3. Code examples
  6. 1. Basic Defensive Database Programming Techniques
    1. Programming Defensively to Reduce Code Vulnerability
      1. Define your assumptions
      2. Rigorous testing
    2. Defending Against Cases of Unintended Use
        1. Handling special characters in searching
        2. Enforcing or eliminating the special characters assumption
    3. Defending Against Changes in SQL Server Settings
      1. How SET ROWCOUNT can break a trigger
        1. The ROWCOUNT assumption
        2. Enforcing and eliminating the ROWCOUNT assumption
        3. Proactively fixing SET ROWCOUNT vulnerabilities
      2. How SET LANGUAGE can break a query
    4. Defensive Data Modification
      1. Updating more rows than intended
      2. The problem of ambiguous updates
        1. Using UPDATE...FROM
        2. Updating inline views
      3. How to avoid ambiguous updates
        1. Using MERGE to detect ambiguity (SQL Server 2008 only)
        2. An ANSI-standard method
        3. Defensive inline view updates
        4. Defensive UPDATE...FROM
    5. Summary
  7. 2. Code Vulnerabilities due to SQL Server Misconceptions
      1. Conditions in a WHERE clause can evaluate in any order
      2. SET, SELECT, and the dreaded infinite loop
      3. Specify ORDER BY if you need ordered data
    1. Summary
  8. 3. Surviving Changes to Database Objects
    1. Surviving Changes to the Definition of a Primary or Unique Key
      1. Using unit tests to document and test assumptions
      2. Using @@ROWCOUNT to verify assumptions
      3. Using SET instead of SELECT when assigning variables
    2. Surviving Changes to the Signature of a Stored Procedure
    3. Surviving Changes to Columns
      1. Qualifying column names
      2. Handling changes in nullability: NOT IN versus NOT EXISTS
      3. Handling changes to data types and sizes
    4. Summary
  9. 4. When Upgrading Breaks Code
    1. Understanding Snapshot Isolation
    2. When Snapshot Isolation Breaks Code
      1. Trigger behavior in normal READ COMMITTED mode
      2. Trigger behavior in SNAPSHOT mode
      3. Building more robust triggers?
    3. Understanding MERGE
    4. Issues When Triggers Using @@ROWCOUNT Are Fired by MERGE
    5. Summary
  10. 5. Reusing T-SQL Code
    1. The Dangers of Copy-and-Paste
    2. How Reusing Code Improves its Robustness
    3. Wrapping SELECTs in Views
    4. Reusing Parameterized Queries: Stored Procedures versus Inline UDFs
    5. Scalar UDFs and Performance
    6. Multi-statement Table-valued UDFs
    7. Reusing Business Logic: Stored Procedure, Trigger, Constraint or Index?
      1. Use constraints where possible
      2. Turn to triggers when constraints are not practical
      3. Unique filtered indexes (SQL Server 2008 only)
    8. Summary
  11. 6. Common Problems with Data Integrity
    1. Enforcing Data Integrity in the Application Layer
    2. Enforcing Data Integrity in Constraints
      1. Handling nulls in CHECK constraints
      2. Foreign key constraints and NULLs
      3. Understanding disabled, enabled, and trusted constraints
        1. Disabled constraints do...nothing
        2. Enabled constraints do not validate existing data
        3. Trusted constraints guarantee that existing data is valid
      4. Problems with UDFs wrapped in CHECK constraints
        1. False negatives: failure during multi-row updates
        2. False positives: allowing an invalid modification
        3. The unique filtered index alternative (SQL Server 2008 only)
        4. The indexed view alternative
    3. Enforcing Data Integrity Using Triggers
        1. Problems with multi-row modifications
        2. Mishandling updates that affect the primary key
        3. Sometimes triggers do not fire
        4. Accidentally overriding changes made by other triggers
        5. Problems with triggers under snapshot isolation levels
    4. Summary
  12. 7. Advanced Use of Constraints
    1. The Ticket-Tracking System
      1. Enforcing business rules using constraints only
      2. Removing the performance hit of ON UPDATE CASCADE
    2. Constraints and Rock Solid Inventory Systems
    3. Adding new rows to the end of the inventory trail
      1. Updating existing rows
      2. Adding rows out of date order
    4. Summary
  13. 8. Defensive Error Handling
    1. Prepare for Unanticipated Failure
    2. Using Transactions for Data Modifications
    3. Using Transactions and XACT_ABORT to Handle Errors
    4. Using TRY...CATCH blocks to Handle Errors
      1. A TRY...CATCH example: retrying after deadlocks
    5. TRY...CATCH Gotchas
      1. Re-throwing errors
      2. TRY...CATCH blocks cannot catch all errors
        1. Killed connections and timeouts
        2. Problems with TRY...CATCH scope
        3. Doomed transactions
    6. Client-side Error Handling
    7. Conclusion