Microsoft® SQL Server® 2008 For Dummies®

Chapter 17. Preserving the Integrity of Your Transactions

Until this point in the book, I've presented each Transact-SQL statement as an isolated event. When I've used more than one statement, they've been a series of independent statements. Sometimes, however, this isn't the behavior you want. In many cases, you'll want a series of SQL statements to occur as an "all or nothing" event. Transactions are the answer to this dilemma. They allow you to bundle independent Transact-SQL statements into a linked bundle.

For example, consider the process used to transfer money between two different bank accounts. You might view the transfer of $50 from account X to account Y as two separate steps:

1. Deduct $50 from account X.

2. Add $50 to account Y.

However, the bank certainly doesn't want these two steps to occur independently, for several reasons:

• If the deduction of funds from account X doesn't succeed (perhaps the balance is less than $50), the bank doesn't want $50 credited to account Y.

• If the addition of funds to account Y doesn't succeed (perhaps account Y doesn't exist or is closed), the bank doesn't want $50 deducted from account X.

Combining these two statements into a single transaction allows the bank to ensure that they happen in the desired "all or nothing" fashion.

In this chapter, you find out how to use the ACID model to preserve transaction integrity and to work with transactions in SQL Server 2008.

Preserving Transaction Integrity with the ACID Model

Database professionals use the ACID ...