Datasheet

do so, you must first delete the row of data containing the foreign key or update the column using a

NULL value. Only then are you able to delete the row containing the primary key.

Referential integrity is based on the relationship between foreign and primary keys and ensures that key

values are consistent across all tables. Referential integrity is automatically enforced by SQL Server and

prevents a user from updating a primary or foreign key in a manner that would break the integrity of

the data.

Indexes

An index is an object associated with tables and is built using one or more columns from a table. An

index stores information from columns (usually primary and foreign key columns) and the exact

location of that data within the table. Thus, using an index to access information in the table is very

efficient, as SQL Server will use the information contained in the index to find the exact location of the

row of data that you want retrieve or update.

SQL Server contains two main types of indexes: clustered and non-clustered.

Clustered indexes sort the data in the table rows by key, providing an efficient means of accessing data in

the table. However, because a clustered index sorts the data in the table, a table can contain only one

clustered index. You can think of a clustered index like a phone book. The columns that define the index

(for example, the last name followed by an initial) are used to sort the table rows. A clustered index

stores the data rows of the table in the bottom leaf of the index. This means that the index consists of the

index entries pointing to each row of data, and the data rows are stored at the end of the index.

Non-clustered indexes store the keys of the table in the index and contain pointers to where the data

actually resides in the table. The pointer in a non-clustered index is called a row locator because it actually

locates the row of data in the table.

Indexes can be unique or not. Unique indexes unique do not allow duplicate keys (keys that contain the

same data value), and indexes that are not defined as unique can contain duplicate keys. Index keys

should not be confused with primary keys in a table. An index key can be generated for any column in a

table that is used to access the data in the table.

The last index that I want to cover is the full-text index. This type of index is used on columns that contain

the

TEXT data type. This is a data type that can store large amounts of data, up to 2 gigabytes worth. This

index enables you to search through the text in a column containing this data type for specific keywords.

Stored procedures

A stored procedure is a single SQL statement or group of SQL statements compiled into an execution plan

and stored under a unique name in the database. It is executed as a unit. A stored procedure can have

multiple SQL statements to perform such tasks as selecting data from one table and updating data in

another table.

Stored procedures increase application performance in a couple of ways. First, they enable fewer SQL

statements to be transmitted across the network, as you send only the name of the stored procedure and

any parameters it may require.

Chapter 1

04_58894x ch01.qxd 10/13/05 5:54 PM Page 6