||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 non-clustered.
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