Skip to content

A unique review of SQL Server index types

Reading Time: 4 minutes

Introduction

One thing I have noticed in various places is that people still tend to use the traditional clustered and non clustered indexes. Yet 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 done this week about index preferences:

As shown rowstore non-clustered filtered index was the least popular choice. 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. 

  1. Rowstore Clustered Index
  2. Rowstore Non-Clustered Indexes
  3. Filtered Rowstore Indexes
  4. XML Indexes
  5. Columnstore Indexes
  6. Clustered ColumnStore Indexes
  7. Hash Indexes
  8. Memory Optimized Non-Clustered Indexes

But before I do I want to briefly discuss heaps.

Heaps

Heaps means that the data for a table does not have any form of clustered index. Traditionally they are know to have issues performance wise. Especially if a large amount of deletes and updates take place inside them.

See for yourself by downloading Brent Ozars First Responder toolkit here and run sp_blitzindex against a database that has heaps. Of course heaps have their uses. They work well with large imports if configured correctly.

Now the heaps are clarified we can go to the index types.

1: Rowstore Clustered Index

You might be wondering what I mean by rowstore. It’s a term somebody came up with to describe the traditional index structure that most people who work with SQL Server know.

It is based on a balanced tree index design. If you look on SQL Server Books online you see the diagram of this index. You see it has a tree structure and looks like an organisation 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 the data itself only one can be created per table.

2: Rowstore Non-Clustered Indexes

These indexes are what some people also call covering indexes. These are indexes that tend to include a subset of the columns in your table to speed up the performance of queries. To reduce the size of these indexes and improve the performance even more you can add columns as “included” columns.

Those columns end up in the leaf level of the index only. It optimises the index in question and avoids SQL Server having to look at another index for that table. You should add columns which are only in the “Select” part of a SQL query as an “included” columns.

You can have a number of these indexes per table depending on which version of SQL you are using.

3: Filtered Rowstore Indexes

Some people will point out this is a type of non-clustered index. And they are completely right. I’m giving this type it’s own section to spread the love for it a bit. 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 query. Doing this makes the index a lot smaller and optimal. If you have a certain query causing you pain it can make it go a lot faster. I’ve seen filtered indexes change queries from taking minutes to seconds.

4: XML indexes

Way back in SQL Server 2005 the decision was made to introduce XML 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. To create a Secondary XML index you must first create a Primary. Either can be used in different ways to access XML data from SQL faster. If you feel sorry for people who support this maybe spare a though for those who support XML in varchar(max) columns as well.

5: Columnstore Indexes

Columnstore indexes were 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 are supported though and the larger varchar data types are not. 

Because of their popularity they have improved with newer versions of SQL Server. Plus filtered columnstore indexes were 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.

7: Hash Indexes

These index types were introduced to handle the data structure used by in-memory OLTP (aka Hekaton). Which first introduced with SQL Server 2014.

These are used by memory optimized tables. Designed to reduce contention during a large amount of transactions.I highly recommend you do your homework before using this. For more information about this I recommend downloading Kalen Delaneys Second Edition about In-Memory OLTP from here.

8: Memory Optimized Non-Clustered Indexes

What a mouthful this index name is! This index type 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 In-Memory OLTP it’s known as a Bw-tree structure.

Final word

Well there’s my quick overview of current index types in SQL Server. A bit longer than expected but worth it. Look in SQL Server books online for more detailed explanations about all of them.

If you have questions or anything to add feel free to comment on this post.

Kevin Chant

Published inSQL Server