Index Types

The following types of indexes are available in SQL Server 2012

  • Clustered
  • Non-clustered (including covering and filtered indexes)
  • XML (primary and secondary)
  • Spatial
  • Full-text
  • Columnstore

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s