What are the different types of indexes, what are the benefits of each?

Question

What are the different types of indexes, what are the benefits of each?

I heard of covering and clustered indexes, are there more? Where would you use them?

Answer 1

• Unique - Guarantees unique values for the column(or set of columns) included in the index
• Covering - Includes all of the columns that are used in a particular query (or set of queries), allowing the database to use only the index and not actually have to look at the table data to retrieve the results
• Clustered - This is way in which the actual data is ordered on the disk, which means if a query uses the clustered index for looking up the values, it does not have to take the additional step of looking up the actual table row for any data not included in the index.

Answer 2

OdeToCode has a good article covering the basic differences

As it says in the article:

Proper indexes are crucial for good performance in large databases. Sometimes you can make up for a poorly written query with a good index, but it can be hard to make up for poor indexing with even the best queries.

Quite true, too... If you're just starting out with it, I'd focus on clustered and composite indexes, since they'll probably be what you use the most.