Datasheet

Chapter 1: Introducing T-SQL and Data Management Systems

RDBMS and Data Integrity

An RDBMS is designed to maintain data integrity in a transactional environment. This is accomplished

through several mechanisms implemented through database objects. The most prominent of these

objects are as follows:

Locks

Constraints

Keys

Indexes

Before I describe these objects in more detail, it ’ s important to understand two other important pieces of

the SQL architecture: connections and transactions.

Connections

A connection is created anytime a process attaches to SQL Server. The connection is established with

defined security and connection properties. These security and connection properties determine which

data you have access to and to a certain degree, how SQL Server will behave during the duration of the

query in the context of the query. For example, a connection can specify which database to connect to on

the server and how to manage memory - resident objects.

Transactions

Transactions are explored in detail in Chapter 10 , so for the purposes of this introduction I will keep the

explanation brief. In a nutshell, a SQL Server transaction is a collection of dependent data modifications

that is controlled so that it completes entirely or not at all. For example, you go to the bank and transfer

$100.00 from your savings account to your checking account. This transaction involves two

modifications — one to the checking account and the other to the savings account. Each update is

dependent on the other. It is very important to you and the bank that the funds are transferred correctly,

so the modifications are placed together in a transaction. If the update to the checking account fails but

the update to the savings account succeeds, you most definitely want the entire transaction to fail. The

bank feels the same way if the opposite occurs.

With a basic idea about these two objects, let ’ s proceed to the four mechanisms that ensure integrity and

consistency in your data.

Locks

SQL Server uses locks to ensure that multiple users can access data at the same time with the

assurance that the data will not be altered while they are reading it. At the same time, the locks are used

to ensure that modifications to data can be accomplished without affecting other modifications or reads

in progress. SQL Server manages locks on a connection basis, which simply means that locks cannot be

held mutually by multiple connections. SQL Server also manages locks on a transaction basis. In the

same way that multiple connections cannot share the same lock, neither can transactions. For example, if

an application opens a connection to SQL Server and is granted a shared lock on a table, that same

application cannot open an additional connection and modify that data. The same is true for

transactions. If an application begins a transaction that modifies specific data, that data cannot be

modified in any other transaction until the first has completed its work. This is true even if the multiple

transactions share the same connection.

❑

CH001.indd 17CH001.indd 17 3/26/10 11:35:41 AM3/26/10 11:35:41 AM