ptg
804
CHAPTER 25 Creating and Managing Indexes
NOTE
If the REBUILD option is run on a heap table (that is, a table with no clustered index),
the rebuild operation does not affect the underlying table. Only the specified nonclus-
tered indexes are rebuilt.
For added flexibility, you can also specify index options as part of the REBUILD operation.
The options available with the REBUILD command are the same options available when
you are creating indexes. The only exception is that the DROP EXISTING option is not
available with the REBUILD operation. (Table 25.2, earlier in this chapter, provides detailed
descriptions of the options.) The following example rebuilds the clustered index on the
Production.Product table and specifies several of the available REBUILD options:
ALTER INDEX [PK_Product_ProductID]
ON [
Production].[Product] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF,
DATA_COMPRESSION = NONE )
An alternative to the REBUILD operation is the REORGANIZE operation. The REORGANIZE
operation is equivalent to the DBCC INDEX DEFRAG command. During the REORGANIZE oper-
ation, the leaf-level pages of the index are physically reordered to match the logical order
of the index keys. The indexes are not dropped. The REORGANIZE operation is always an
online operation and does not require long-term table locks to complete.
TIP
The REORGANIZE operation can generate a large number of transactions during its exe-
cution. You need to be sure to carefully evaluate the amount of space available in the
transaction log and monitor the free space during this operation. If the transaction log
is set to AUTOGROW, you need to make sure you have adequate free space on the drive
where your transaction log lives. This is especially true for very large tables. Several

you can rebuild the index to make it available again.
TIP
Another reason for disabling a nonclustered index is to reduce the space requirements
when rebuilding the index. If an index to be rebuilt is not disabled, SQL Server
requires enough temporary disk space in the database to store both the old and new
versions of the index. However, if the index is disabled first, SQL Server can reuse the
space required for the disabled index to rebuild it. No additional disk space is neces-
sary except for temporary space required for sorting, which is only about 20% of the
index size.
The following example disables a nonclustered index on the Production.Product table:
ALTER INDEX [AK_Product_Name] ON [Production].[Product] DISABLE
One point to keep in mind when an index is disabled is that it is not readily apparent in
SSMS that the index has been disabled. The index still appears in the Object Explorer tree
under the Indexes node, and there are no indicators on the index display to alert you to
the fact that it has been disabled. You can, however, use other methods to determine if the
index has been disabled. The sys.indexes catalog view is one of these methods. Refer to
the is_disabled column returned with this view. A value of 1 in the is_disabled column
indicates that it has been disabled, and a value of 0 indicates that it is enabled. The follow-
ing SELECT statement shows an example of how to use the sys.indexes catalog view:
select is_disabled,* from sys.indexes
where object_name(object_id) = ‘Product’
Download from www.wowebook.com
ptg
806
CHAPTER 25 Creating and Managing Indexes
FIGURE 25.6 Using Object Explorer to manage indexes.
You can also easily change options on an index with the ALTER INDEX statement. The
following example sets several of the available options for a nonclustered index on the
authors table:
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product]

index columns and other relevant information. This option was not available with the
SQL Server 2000 Object Explorer. You can also run the SP_HELPINDEX command on
any table in a database to list all the indexes on the table and their related columns.
This command must be run in a database engine query window. For example,
sp_helpindex [Production.Product] returns all the indexes for the Product table
in the AdventureWorks database. Make sure to enclose the table name with brackets
when including the schema name.
Dropping Indexes
You can drop indexes by using T-SQL or via tools in the SSMS. To drop indexes with T-
SQL, you use the DROP INDEX command, a simple example of which follows:
DROP INDEX [IX_WorkOrder_ScrapReasonID] ON [Production].[WorkOrder]
This command drops the index named IX_WorkOrder_ScrapReasonID on the
Production.WorkOrder table.
Using the Object Explorer in SSMS is the simplest alternative for dropping indexes. In the
Object Explorer, you simply right-click the index you want to drop and then select Delete.
The same execution options available for adding and modifying indexes are also available
after you select Delete. This includes the option to script the T-SQL statements like that
shown in the preceding DROP INDEX example.
NOTE
If you drop a clustered index on a table, SQL Server needs to rebuild all the remaining
nonclustered indexes on the table. The reason is that when a clustered index exists on
a table, the nonclustered indexes include the clustered index key in the nonclustered
index rows as a pointer to the corresponding data rows. When the clustered index is
dropped, the clustered index key needs to be replaced with page and row pointers. If a
large number of nonclustered indexes exist on the table, the operation to rebuild the
nonclustered indexes can be very time consuming and I/O intensive. For more informa-
tion on the internal structures of clustered and nonclustered indexes, see Chapter 34.
Online Indexing Operations
One of the great features available with SQL Server 2008 is online indexing. This feature,
available only with the Enterprise or Developer Edition, allows you to create, rebuild, or

structures are invalidated. Future queries utilize the newly created index structures after
this point.
When considering online indexing, you need to account for the following:
. Disk space—Generally, the disk space requirements for online operations are the
same as those for offline operations. The exception to this is online index operations
on clustered indexes. These operations use a temporary mapping index that requires
additional disk space. The temporary mapping index contains one row for each
record in the table.
. Performance—Online index operations are generally slower and take more system
resources than offline operations. Primarily, the reason is that the old and new index
structures are maintained during the index operation. Heavy updates to the tables
involved in the index operation can cause an overall decrease in performance and a
spike in CPU utilization and I/O as the two index structures are maintained.
. Transaction log—Online index operations are fully logged. You may therefore
encounter a heavy burden on your transaction log during online index operations
Download from www.wowebook.com
ptg
809
Indexes on Views
25
for large tables. This can cause your transaction log to fill quickly. The transaction
log can be backed up, but it cannot be truncated during online index operations.
You need to make sure you have enough space for your log to grow; otherwise, the
online index operation could fail.
Indexes on Views
SQL Server 2008 supports the creation of indexes on views. Like indexes on tables, indexes
on views can dramatically improve the performance of the queries that reference the
views. By nature, a view is a virtual table and does not have a separate data structure as
does a table, even though it can be referenced like a table. After an index is created on a
view, the result set of the view is stored in the database, just as it would be for a table. The

810
CHAPTER 25 Creating and Managing Indexes
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
The SCHEMABINDING clause and database schema qualifier (dbo) for each table are necessary
in the view definition to be able to make the view valid for index creation. The following
example creates an index on the titleview view:
CREATE UNIQUE CLUSTERED INDEX [AK_vw_Employee] ON [dbo].[vw_Employee]
( [JobTitle] ASC, [LoginID] ASC)
After the index is created, you can manage it in much the same way that you manage the
indexes on tables. You can use both T-SQL and SSMS to manage these indexes.
For more information and guidelines on creating and using indexed views, see Chapter 27.
Summary
Index creation is an important part of managing a database. Creating useful indexes can
vastly improve query performance and should not be overlooked. Fortunately, SQL Server
2008 makes the creation and management of indexes quite easy.
In Chapter 26, “Implementing Data Integrity,” you see how you can use indexes and other
methods to enforce data integrity. Subsequent chapters cover the internal working of
indexes and give you more insight into their role in performance.
Download from www.wowebook.com
ptg
CHAPTER 26
Implementing Data
Integrity
IN THIS CHAPTER
. What’s New in Data Integrity
. Ty pes of Data Integrity
. Enforcing Data Integrity
. Using Constraints
. Rules

812
CHAPTER 26 Implementing Data Integrity
Types of Data Integrity
How integrity is enforced depends on the type of integrity being enforced. As described in
the following sections, the types of data integrity are domain, entity, and referential
integrity.
Domain Integrity
Domain integrity controls the validation of values for a column. You can use domain
integrity to enforce the type, format, and possible values of data stored in a column. SQL
Server provides several mechanisms to enforce domain integrity:
. You can control the type of data stored in a column by assigning a data type to
the column.
. You can use CHECK constraints and rules to control the format of the data.
. You can control the range of values stored in a column by using FOREIGN KEY con-
straints, CHECK constraints, default definitions, nullability, and rules.
Entity Integrity
Entity integrity requires that all rows in a table be unique. You can enforce entity integrity
in SQL Server by using PRIMARY KEY constraints, UNIQUE constraints, and IDENTITY proper-
ties.
Referential Integrity
Referential integrity preserves the defined relationships between tables. You can define such
a relationship in SQL Server by relating foreign key columns on one table to the primary
key or unique key of another table. When it is defined, referential integrity ensures that
values inserted in the foreign key columns have corresponding values in the primary
table. It also controls changes to the primary key table and ensures that related foreign key
rows are not left orphaned.
Enforcing Data Integrity
You can enforce data integrity by using declarative or procedural methods. Implementing
declarative data integrity requires little or no coding. Implementing procedural data
integrity is more flexible but requires more custom coding.

values in all the primary key columns must be unique. Duplication can exist in a single
column that is part of a multicolumn primary key.
There can be only one primary key defined for each table. When a primary key is defined
on a table, a unique index is automatically created as well. This index contains all the
columns in the primary key and ensures that the rows in this index are unique. Generally,
every table in a database should have a primary key. The primary key and its associated
unique index provide fast access to a database table.
Figure 26.1 shows the
Adventureworks2008 database Employee table, which is an example
of a table that has a primary key defined. The primary key in this table is EmployeeID, and
it is denoted in the dialog shown in Figure 26.1 with a key symbol in the leftmost
column.
The existing primary key on the Employee table in the Adventureworks2008 database was
generated as a T-SQL script, as shown in the following example:
ALTER TABLE [HumanResources].[Employee]
ADD CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED
(BusinessEntityID ASC)
In general, you try to choose a primary key that is relatively short. BusinessEntityID, for
example, is a good choice because it is an integer column and takes only 4 bytes of
Download from www.wowebook.com