Database Reference
In-Depth Information
to account for maintenance when the database is deployed. Staying on top
of index maintenance from day one will prevent emergency situations later,
when the database is huge and query performance has ground to a halt.
Now it's time to start creating some indexes. Although indexes, in some
form or another, exist in all of the major RDBMSs, SQL Server has spe-
cific syntax and capabilities that are important to know. In this section, we
explain how to create indexes on our tables, discuss things to keep in mind,
and describe the basic maintenance that must be performed.
Naming Guidelines
As with most objects in the database, a good naming guideline is very im-
portant. It helps not only you as the designer/developer but also others
downstream. As a basic guideline, establish a naming scheme wherein you
can visually identify the index as being clustered or nonclustered and de-
termine the columns it may include. We typically use a combination of a
prefix, the table name, and the column name.
For example, on the Customers table, we might have an index named
idx_Customer_LastName_FirstName
In this case, the
idx
tells us it's an index, and then it's followed by the
table name and column names. You could also use
ixc
to denote a clus-
tered index, if the primary key of the table isn't also the clustered index. As
with any of the naming standards, the specifics aren't as important as hav-
ing the standard in the first place.
Creating Indexes
When you create an index on a table, you can specify it either in the cre-
ate statement for the table (only if it is the primary key or a unique index),
or after the table is created using the
CREATE INDEX
statement. Following
is a simple create statement showing the most commonly used options
(SQL Server 2008 Books Online has an exhaustive reference on the syntax
of this statement).
Search WWH ::
Custom Search