Introduction
I want to do a unique review of SQL Server index types in this post. In order to help raise awareness about some of them.
Because one thing I have noticed in various places is that people still tend to use the traditional clustered and non clustered indexes. However, I rarely see the filtered indexes being used. Even though they have been available since SQL Server 2008.
Here’s the results of a quick poll I had done previously about index preferences:
As shown above rowstore non-clustered filtered index was the least popular choice. Of course, one of the votes you see might have been a sympathy vote.
So, to help spread the love for them and newer index types I have decided to do a unique review of current SQL Server index types.
- Rowstore Clustered Index
- Rowstore Non-Clustered Indexes
- Filtered Rowstore Indexes
- XML Indexes
- Columnstore Indexes
- Clustered Columnstore Indexes
- Hash Indexes
- Memory Optimized Non-Clustered Indexes
- Full Text Indexes
But before I cover the indexes, I want to briefly discuss heaps.
Heaps
A heap is the term used for data within a table that does not have any form of clustered index. It’s easy to remember if you think of all the data just heaped together with no structure.
Heaps are traditionally known to have issues performance wise. Especially if a large amount of deletes and updates take place inside of them.
You can see this for yourself by downloading Brent Ozars First Responder toolkit from here. Afterwards, you can run sp_blitzindex against a database that has heaps.
Of course, heaps have their uses. In fact, they can work well with large imports if configured correctly.
Now that heaps are covered, we can go to the index types.
1: Rowstore Clustered Index
You might be wondering what I mean when I say rowstore. To clarify, it’s a term somebody came up with to describe the traditional index structure that most people who work with SQL Server know. Instead of the newer Columnstore structure.
It is based on a balanced tree index design that has been around for many years. In fact, if you look at SQL Server index architecture guide here you can see the diagram of it’s structure.
As you can see it has a tree structure and like an organization chart. Clustered indexes define how the data in a table is ordered by stating what columns the index is ordered by when created. Index keys are another name for the columns used to order an index.
Traditionally, these are created to convert the data inside heaps into a structured index. Because they contain all the data in the table itself, only one can be created per table.
2: Rowstore Non-Clustered Indexes
Now, these indexes are what some people also call covering indexes. In reality, these indexes tend to include a subset of the columns in your table to speed up the performance of queries.
Doing this helps to reduce the size of these indexes and improve the performance even more you can add other columns as “included” columns.
Included columns end up in the leaf level of the index only. It optimizes the index in question and avoids SQL Server having to look at another index for that table. Rule of thumb is that you should only add columns which are only in the “Select” part of a SQL query as an “included” column.
You can have a varied number of these indexes per table depending on which version of SQL Server you are using.
3: Filtered Rowstore Indexes
Some people will point out this is a type of non-clustered index. Of course, they are completely right. I’m giving this type its own section to spread the love for it a bit more. Because it is so useful.
It’s being available since SQL Server 2008. I think it should be more popular because it can dramatically improve query performance.
It’s where you create a non-clustered index with a “Where” clause at the end after stating all the columns. Exactly like you do at the end of a normal T-SQL query.
Doing this can make the index a lot smaller and optimal. In fact, if you have a certain query causing you pain it can make it go a lot faster. For instance, I’ve seen filtered indexes change queries from taking minutes to seconds.
It was first introduced for use with rowstore indexes. However, you can now use it with Columnstore indexes as well.
4: XML indexes
Way back when SQL Server 2005 was released the decision was made to introduce XML as a datatype to SQL Server. Back then it was a big deal. I’ll be honest here; I’ve not used them since Microsoft reduced their importance in the SQL exams.
Still, I did say I’d cover them.
You can have a Primary and an additional Secondary XML index. You must first create a Primary XML index in order to create a Secondary XML index. Either can be used in different ways to access XML data from SQL faster.
If you feel sorry for people who support this in Production, maybe you should spare a though for those who support XML in varchar(max) columns as well.
5: Columnstore Indexes
Columnstore indexes were first introduced in SQL Server 2012 and are highly compressed non-clustered indexes. Based on a compression engine used in Excel these are ideal for reading large database tables that contains millions of rows.
I have to add that only certain data types were first supported though, and the larger varchar data types were not originally supported. However, that has changed with newer versions of SQL Server as you can see here.
Because of their popularity they have improved with newer versions of SQL Server. Plus, filtered Columnstore indexes were eventually introduced with SQL Server 2016.
Another advantage of using these indexes are that they perform well with memory optimized tables.
6: Clustered Columnstore Indexes
First introduced in SQL Server 2014 a table with compatible data types can have a clustered Columnstore index.
Be aware that when clustered Columnstore index is created it does not reorder the data in a specified order. Therefore, it is considered good practice to create a clustered index on a large table first. After that convert it to Columnstore.
Like their rowstore counterparts, you can only have one of these per table. If you are lucky enough to use SQL Server 2017 or above, you can also create them on tables that have LOB data types now. For example, tables that have varchar(max) columns.
7: Hash Indexes
Hash indexes were introduced to handle the data structure used by In-Memory OLTP (also known as Hekaton). Which is a feature that was first introduced with SQL Server 2014.
In fact, they are especially designed for use with memory optimized tables. Designed to reduce contention during a large number of transactions.
I highly recommend you do your homework before using In-Memory OLTP.
For more information about this, I recommend downloading Kalen Delaney’s Second Edition about In-Memory OLTP from here. Alternatively, you can download the newer white paper that is based on SQL Server 2016 here.
8: Memory Optimized Non-Clustered Indexes
Well, this index name is certainly a mouthful! As the name suggests, this type of index is a version of a non-clustered index for Memory-Optimized tables. It’s based on the traditional B-Tree, or Balanced Tree, index design.
It also uses the same top-down design that rowstore indexes have. Due it being optimized for use with In-Memory OLTP it’s known as a Bw-tree structure.
9. Full Text indexes
Initially, I didn’t add Full Text indexes to this post. However, since they are heavily used so I decided to include them.
Basically, creating Full Text indexes allows you to query data that contains large strings within SQL Server tables.
Previously, the Full-Text engine that manages these indexes had to be installed as a separate service. However, from SQL Server 2008 it’s now part of the Database Engine.
You can read more about how to use these indexes for string searches in detail here.
Final word
Well, there’s my unique review of SQL Server index types. It’s a bit longer than I expected it to be, but it was worth it.
Look in SQL Server Documentation online here for more detailed explanations about all of them.
If you have questions or anything else to add feel free to reach out to me.
