Datasheet

Chapter 1 ✦ The Information Architecture Principle

✦ Wrong data

✦ Incomplete data

✦ Questionable data

✦ Inconsistent data

The quality of the data depends upon the people modifying the data. Data security —

controlling who can view or modify the data— is also an aspect of data integrity.

Transactional Integrity

A transaction is a single logical unit of work, such as inserting 100 rows, updating 1,000 rows,

or executing a logical set of updates. The quality of a database product is measured by its

transactions’ adherence to the ACID properties. ACID, you might recall, is an acronym for four

interdependent properties: atomicity, consistency, isolation, and durability. Much of the

architecture of SQL Server is founded on these properties. Understanding the ACID proper-

ties of a transaction is a prerequisite for understanding SQL Server:

✦ The transaction must be atomic, meaning all or nothing. At the end of the transaction

either all of the transaction is successful or all of the transaction fails. If a partial trans-

action is written to disk, the atomic property is violated.

✦ The transaction must preserve database consistency, which means that the database

must begin in a state of consistency and return to a state of consistency once the trans-

action is complete. For the purposes of ACID, consistency means that every row and

value must agree with the reality being modeled, and every constraint must be

enforced. If the order rows are written to disk but the order detail rows are not written,

the consistency between the

Order and the OrderDetail is violated.

✦ Each transaction must be isolated, or separated, from the effects of other transactions.

Regardless of what any other transaction is doing, a transaction must be able to con-

tinue with the exact same data sets it started with. Isolation is the fence between two

transactions. A proof of isolation is the ability to replay a serialized set of transactions

on the same original set of data and always receive the same result.

For example, assume Joe is updating 100 rows. While Joe’s transaction is under way,

Sue deletes one of the rows Joe is working on. If the delete takes place, Joe’s transac-

tion is not sufficiently isolated from Sue’s transaction. This property is less critical in a

single-user database than in a multi-user database.

✦ The durability of a transaction refers to its permanence regardless of system failure.

Once a transaction is committed it stays committed. The database product must be

constructed so that even if the data drive melts, the database can be restored up to the

last transaction that was committed a split second before the hard drive died.

The nemesis of transactional integrity is concurrency— multiple users simultaneously

attempting to retrieve and modify data. Isolation is less of an issue in small databases, but in

a production database with thousands of users, concurrency competes with transactional

integrity. The two must be carefully balanced; otherwise, either data integrity or performance

will suffer.

SQL Server’s architecture meets all the transactional-integrity ACID properties, providing that

you, as the developer, understand them, develop the database to take advantage of SQL

Server’s capabilities, and the DBA implements a sound recovery plan. A synergy exists among

05_542567 ch01.qxp 9/27/06 9:58 PM Page 13