An index is a way to mark data in such a way to make it faster to find than a full table scan. Indexes are designed to minimize the number of read operations required to find a given row of data. A structure known as a “balanced tree” divides the data in such a way that no more than a few reads should ever be required to find the data.
However, the index should be built on the column or columns primarily used for searching so it is important to know how the data will be accessed in order to maximize the benefit of an index.
Clustered indexes are most typical and arguably every table should have one based on the primary key. Multiple columns can be used as the “cluster key” and this will return data based on the order of the columns defined in the cluster key. This imposes a predefined sort order of the data in the table. If searches generally correspond to the priority and order of the data defined by the cluster key, you have designed a good clustered index. Again, it depends on how the data is typically accessed as to the best design of your index. You can only have ONE clustered index on a table.
Non-clustered indexes do not impose a sort order on the table columns, so you can create multiple non-clustered indexes on a table. This is good if you have many searches on the data that are significant but fall outside the typical usage that the clustered index doesn’t accommodate well or at all.
Covering indexes allow you to tack on additional columns of data other than the column defined in the index key. This allows users to search on a column and return additional data from the table with it without having to go to the actual table to pull the additional data. For example, if you know people search phone numbers, you might build an index on the phone number but also include name and address as part of the covering index. This way, additional reads won’t be required to go to the table to grab the name and address after the phone number is found.
A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less-selective range.
Indexes are not a cure or counter-balance to bad code. You must know how the data is used to design good indexes. Indexes can be helpful but too many indexes can increase overhead dramatically and actually make things slower than they would be without them.
Design well and test performance after each index.