Performance is an important consideration when designing and creating your database. Indexes can dramatically improve the performance of statements that search for a specific row or a specific subset of the rows. On the other hand, indexes take up additional disk space and can slow inserts, updates, and deletes.
Choosing a set of indexes
When to use indexes
Use indexes for frequently-searched columns
Using clustered indexes
Index information in the catalog