Professional SQL Server™ 2005 Programming

8.7. Summary

Indexes are sort of a cornerstone topic in SQL Server or any other database environment, and are not something to be taken lightly. They can drive your performance successes, but they can also drive your performance failures.

Top-level things to think about with indexes:

• Clustered indexes are usually faster than non-clustered indexes (one could come very close to saying always, but there are exceptions).

• Only place non-clustered indexes on columns where you are going to get a high level of selectivity (that is, 95 percent or more of the rows are unique).

• All Data Manipulation Language (DML: INSERT, UPDATE, DELETE, SELECT) statements can benefit from indexes, but inserts, deletes, and updates (remember, they use a delete and insert approach) are slowed by indexes. The lookup part of a query is helped by the index, but anything that modifies data will have extra work to do (to maintain the index in addition to the actual data).

• Indexes take up space.

• Indexes are used only if the first column in the index is relevant to your query.

• Indexes can hurt as much as they help — know why you're building the index, and don't build indexes you don't need.

• Indexes can provide structured data performance to your unstructured XML data, but keep in mind that, like other indexes, there is overhead involved.

When you're thinking about indexes, ask yourself these questions:

Question	Response
Are there a lot of inserts or modifications to this table?	If yes, keep indexes to a minimum. This kind ...