When you are working with index selection you may want to ask these questions:
What indexes are associated currently with a given table?
What are the most important processes that make use of the table?
What is the ratio of select operations to data modifications performed on the table?
Has a clustered index been created for the table?
Can the clustered index be replaced by a nonclustered index?
Do any of the indexes cover one or more of the critical queries?
Is a composite index required to enforce the uniqueness of a compound primary key?
What indexes can be defined as unique?
What are the major sorting requirements?
Do some queries use descending ordering of result sets?
Do the indexes support joins and referential integrity checks?
Does indexing affect update types (direct versus deferred)?
What indexes are needed for cursor positioning?
If dirty reads are required, are there unique indexes to support the scan?
Should IDENTITY columns be added to tables and indexes to generate unique indexes? Unique indexes are required for updatable cursors and dirty reads.
When deciding how many indexes to use, consider:
Space constraints
Access paths to table
Percentage of data modifications versus select operations
Performance requirements of reports versus OLTP
Performance impacts of index changes
How often you can use update statistics
