Defensive Database Programming with SQL Server

Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment. Too often as developers, we stop work as soon as our code passes a few basic tests to confirm that it produces the 'right result' in a given use case. We do not stop to consider what other possible ways in which the code might be used in the future, or how our code will respond to common changes to the database environment, such as a change in the database language setting, or a change to the nullability of a table column, and so on. In the short-term, this approach is attractive; we get things done faster. However, if our code is designed to be used for more than just a few months, then it is very likely that such changes can and will occur, and the inevitable result is broken code or, even worse, code that silently starts to behave differently, or produce different results. When this happens, the integrity of our data is threatened, as is the validity of the reports on which critical business decisions are often based. At this point, months or years later, and long after the original developer has left, begins the painstaking process of troubleshooting and fixing the problem. Would it not be easier to prevent all this troubleshooting from happening? Would it not be better to spend a little more time and effort during original development, to save considerably more time on troubleshooting, bug fixing, retesting, and redeploying? This is what defensive programming is all about: we learn what can go wrong with our code, and we proactively apply this knowledge during development. This book is filled with practical, realistic examples of the sorts of problems that beset database programs, including: - Changes in database objects, such as tables, constraints, columns, and stored procedures. - Changes to concurrency and isolation levels. - Upgrades to new versions of SQL Server. - Changes in requirements. - Code reuse. - Problems causing loss of data integrity. - Problems with error handling in T-SQL. In each case, it demonstrates approaches that will help you understand and enforce (or eliminate) the assumptions on which your solution is based, and to improve its robustness. Ultimately, the book teaches you how to think and develop defensively, and how to proactively identify and eliminate potential vulnerabilities in T-SQL code. Introduction 11 What this book covers 12 What this book does not cover 17 Code examples 17 Chapter 1: Basic Defensive Database Programming Techniques 19 Programming Defensively to Reduce Code Vulnerability 20 Define your assumptions 20 Rigorous testing 21 Defending Against Cases of Unintended Use 22 Defending Against Changes in SQL Server Settings 29 How SET ROWCOUNT can break a trigger 30 How SET LANGUAGE can break a query 38 Defensive Data Modification 43 Updating more rows than intended 43 The problem of ambiguous updates 45 How to avoid ambiguous updates 49 Summary 55 Chapter 2: Code Vulnerabilities due to SQL Server Misconceptions 57 Conditions in a WHERE clause can evaluate in any order 57 SET, SELECT, and the dreaded infinite loop 64 Specify ORDER BY if you need ordered data 72 Summary 74 Chapter 3: Surviving Changes to Database Objects 77 Surviving Changes to the Definition of a Primary or Unique Key 78 Using unit tests to document and test assumptions 82 Using @@ROWCOUNT to verify assumptions 85 Using SET instead of SELECT when assigning variables 86 Surviving Changes to the Signature of a Stored Procedure 88 Surviving Changes to Columns 91 Qualifying column names 91 Handling changes in nullability: NOT IN versus NOT EXISTS 95 Handling changes to data types and sizes 100 Summary 103 Chapter 4: When Upgrading Breaks Code 105 Understanding Snapshot Isolation 106 When Snapshot Isolation Breaks Code 110 Trigger behavior in normal READ COMMITTED mode 113 Trigger behavior in SNAPSHOT mode 118 Building more robust triggers? 122 Understanding MERGE 123 Issues When Triggers Using @@ROWCOUNT Are Fired by MERGE 125 Summary 130 Chapter 5: Reusing T-SQL Code 131 The Dangers of Copy-and-Paste 132 How Reusing Code Improves its Robustness 137 Wrapping SELECTs in Views 141 Reusing Parameterized Queries: Stored Procedures versus Inline UDFs 141 Scalar UDFs and Performance 147 Multi-statement Table-valued UDFs 151 Reusing Business Logic: Stored Procedure, Trigger, Constraint or Index? 152 Use constraints where possible 152 Turn to triggers when constraints are not practical 154 Unique filtered indexes (SQL Server 2008 only) 160 Summary 160 Chapter 6: Common Problems with Data Integrity 163 Enforcing Data Integrity in the Application Layer 163 Enforcing Data Integrity in Constraints 166 Handling nulls in CHECK constraints 168 Foreign key constraints and NULLs 171 Understanding disabled, enabled, and trusted constraints 173 Problems with UDFs wrapped in CHECK constraints 180 Enforcing Data Integrity Using Triggers 192 Summary 207 Chapter 7: Advanced Use of Constraints 209 The Ticket-Tracking System 210 Enforcing business rules using constraints only 211 Removing the performance hit of ON UPDATE CASCADE 221 Constraints and Rock Solid Inventory Systems 227 Adding new rows to the end of the inventory trail 237 Updating existing rows 245 Adding rows out of date order 249 Summary 254 Chapter 8: Defensive Error Handling 255 Prepare for Unanticipated Failure 255 Using Transactions for Data Modifications 257 Using Transactions and XACT_ABORT to Handle Errors 262 Using TRY...CATCH blocks to Handle Errors 266 A TRY...CATCH example: retrying after deadlocks 267 TRY...CATCH Gotchas 273 Re-throwing errors 273 TRY...CATCH blocks cannot catch all errors 278 Client-side Error Handling 285 Conclusion 290 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.