8 Weeks of Indexes: Types of Indexes


This is the 3rd post in a series entitled “8 Weeks of Indexes”.  The purpose of this series was more for me to learn more about indexes. 

There are many types of indexes used in Microsoft SQL. I could easily use up the remainder of my weekly posts to discuss each one in depth, but in order to actually get to the meat and bones of how to use indexes, this post is an overview of the more commonly used indexes.

Each Edition of MS SQL from 2005 and up, continues to add types of index to the engine. SQL 2005 had a total of 7 types of indexes, SQL 2008 added 1 additional type, SQL 2012 has a total of 10 types of indexes and SQL 2014 lists 12 types of different indexes.

My personal experiences with index usage unfortunately has been limited to numbers 1-6 for SQL 2005. I currently do not manage any data store with spatial data, so I haven’t had a need to use that one.  I am really excited about experimenting with Filtered Indexes any I do not (yet) have a SQL 2012 server in production, so I have not looked at the two new added indexes.

Here is the break down of each type of index by SQL Engine. Highlighted indexes are new to that specific edition. 

SQL 2005

  1. Clustered – based on a column or multiple columns, this index determines the physical sort order of how the data is physically written to the data base.  A clustered index for a phone book would be on “Last Name”
  2. Non-clustered – can be created on any column or multiple columns in addition to a Clustered index. this index create a “pointer” to find what row data is located on. If you wanted to know all the “Robert”s in a phone book, you could create a Non-Clustered index on First Name column.
  3. Unique – this ensures that either a clustered or non-clustered indexes key does not have duplicate values.
  4. Index with included columns – extends the functionality of non-clustered indexes to include “non-key” items in the index.
  5. Indexed Views – a unique way to present structured, indexed data in a view rather than a table. can use Non-Clustered as well, but only after a Clustered index was created.
  6. Full-text – a special type of index used exclusively with the Microsoft Full-Text Engine to facilitate word searches within data columns.
  7. XML – a way to index XML data type columns

SQL 2008 and 2008 R2

  1. Clustered
  2. Non-Clustered
  3. Unique
  4. Index with included columns
  5. Full-Text
  6. Spatial  provides the ability to index “spatial data” in a column of geometry data type.
  7. Filtered—provides a way to filter out unnecessary rows of a commonly used subset of data
  8. XML

SQL 2012

  1. Clustered
  2. Non-Clustered
  3. Unique
  4. Column store – based on vertical partitioning of the data by columns
  5. Index with included columns
  6. Index on computed columns – an index on a column that is derived of one or more columns
  7. Filtered
  8. Spatial
  9. XML
  10. Full-Text

SQL 2014

  1. Hash – an index that provides data through an in-memory hash table
  2. memory-optimized nonclustered indexes – used in the new feature of “in memory optimized tables”
  3. Clustered
  4. Non-clustered
  5. Unique
  6. Column store
  7. Index with Included columns
  8. Index on computed columns
  9. Filtered
  10. Spatial
  11. XML
  12. Full-Text
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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