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.
- 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”
- 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.
- Unique – this ensures that either a clustered or non-clustered indexes key does not have duplicate values.
- Index with included columns – extends the functionality of non-clustered indexes to include “non-key” items in the index.
- 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.
- Full-text – a special type of index used exclusively with the Microsoft Full-Text Engine to facilitate word searches within data columns.
- XML – a way to index XML data type columns
SQL 2008 and 2008 R2
- Index with included columns
- Spatial – provides the ability to index “spatial data” in a column of geometry data type.
- Filtered—provides a way to filter out unnecessary rows of a commonly used subset of data
- Column store – based on vertical partitioning of the data by columns
- Index with included columns
- Index on computed columns – an index on a column that is derived of one or more columns
- Hash – an index that provides data through an in-memory hash table
- memory-optimized nonclustered indexes – used in the new feature of “in memory optimized tables”
- Column store
- Index with Included columns
- Index on computed columns