||SQL Server Tips by Gama and Naughter
The concept behind indexes is to
change the order of the data (clustered index) or to add metadata
(non-clustered index) for improving the performance of queries.
* Physically stored in order
(ascending or descending)
* Only one per table
* When a primary key is
created a clustered index is automatically created as well.
* If the table is under heavy
data modifications or the primary key is used for searches, a
clustered index on the primary key is recommended.
* Columns with values that
will not change at all or very seldom, are the best choices.
* Up to 249 nonclustered
indexes are possible for each table or indexed view.
* The clustered index keys are
used for searching therefore clustered index keys should be
chosen with a minimal length.
* Covered queries (all the
columns used for joining, sorting or filtering are indexed)
should be non-clustered.
* Foreign keys should be
If the table is under heavy data
retrieval from fields other than the primary key, one clustered
index and/or one or more non-clustered indexes should be created for
the column(s) used to retrieve the data.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter