Effective index design is very important in achieving good SQL Server performance. Because indexes are not considered part of the logical database design, they can be dropped, added, and changed without affecting the database schema. Experimenting with indexes will affect the performance only of a database or application.
The SQL Server query optimizer can use an index to reduce the number of logical page I/Os required to execute an SQL statement. (A logical page I/O will result in a physical page I/O from the hard disk only when the page is not in the cache.) When no indexes are available, SQL Server must perform a table scan, reading every data page in the table. When an index is available, and the optimizer determines that using that index will result in fewer logical and physical page I/Os than a table scan, it will use that index. Indexes that are likely to be used by the optimizer are called useful indexes.
The optimizer will reliably choose the most useful index in the majority of cases. This means that you must anticipate the types of queries required and define useful indexes to minimize table scans. The overall strategy should be to provide a good selection of indexes to the SQL Server optimizer, and trust it to make the right decision. This reduces analysis time and gives good performance over a wide variety of situations.