The following types of indexes are available in SQL Server 2012
- Non-clustered (including covering and filtered indexes)
- XML (primary and secondary)
It’s obvious when you know about the different types of indexes but the 70-462 guide is the first place I’ve seen a list of which indexes are best used in which situations so I thought I’d list them here.
Clustered indexes are best when querying…
- a large percentage of columns in the table
- a single row based on the clustered index key
- range-based data
Non-clustered indexes are best when querying…
- only a few rows in a large table
- data that is covered by the index
Filtered indexes are best…
- when combined with sparse columns (columns with lots of NULL values) to find specific non-NULL rows
- with queries where a small subset of rows are queried often
More information on indexes can found in Chapter 10, Lesson 1 of the exam guide.