Datasheet

Chapter 1 ✦ The Information Architecture Principle

Each transaction is stuck waiting for the other to release its lock, and neither can complete

until the other does. Unless an outside force intercedes, or one of the transactions gives up

and quits, this situation could persist until the end of time.

Chapter 51, “Managing Transactions, Locking, and Blocking,” includes walk-through exam-

ples of the transactional faults, isolation levels, and SQL Server locking.

Isolation Levels

At the physical level, any database engine that permits logical transactions must provide a

way to isolate those transactions. The level of isolation, or the height of the fence between

transactions, can be adjusted to control which transactional faults are permitted. The ANSI

SQL-92 committee specifies four isolation levels: read uncommitted, read committed, repeat-

able read, and serializable.

In addition, Microsoft added snapshot isolation to SQL Server 2005. Essentially, snapshot iso-

lation makes a virtual copy, or snapshot, of the first transaction’s data, so other transactions

do not affect it. This method can lead to lost updates.

The Value of Null

The relational database model represents missing data using the special value of null. The

common definition of null is “unknown”; however, null can actually represent three subtly dif-

ferent scenarios of missing data:

✦ The column does not apply for this row— for example, if the person is not employed,

then any value in the

EmploymentDate column would be invalid.

✦ The data has not yet been entered, but likely will, such as in a contact row that has the

name and phone number, and will hopefully have the address once a sale is made.

✦ The column for this row contains a value of “nothing”— for example, a comment col-

umn is valid for every row but may be empty for most rows.

Depending on the type of missing data, some designers use surrogate nulls (blanks, zeroes, or

n/a) instead. However, multiple possible values for missing data can create consistency prob-

lems when querying data.

The nullability of a column, whether or not the column accepts nulls, may be defined when a

column is created. Note that by default, SQL Server does not allow nulls, but the ANSI stan-

dard does.

Working with Nulls

Because null has no value, the result of any expression that includes null will also be

unknown. If the contents of a bank account are unknown and its funds are included in a port-

folio, the total value of the portfolio is also unknown. The same concept is true in SQL, as the

following code demonstrates. Phil Senn, a wise old database developer, puts it this way:

“Nulls zap the life out of any other value.” As proof:

SELECT 1 + NULL

Result:

NULL

Cross-

Reference

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