Overview
Although there are many different things that can be done to improve the performance of a database system, creating an index is the main tool in every DBA’s toolbox that is used when we are trying to improve the performance of a query. Having the right index on a table for a query can make the difference between the query taking seconds versus hours to be executed. With more and more features being added to SQL Server with each release, we now have many different types of indexes that we can create in order to make queries execute as fast as possible.
This tutorial will try to go through each type of index available in SQL Server and explain why/when each one could be used to improve your query performance.
Explanation
This first couple sections of this tutorial will dive into the main index types that even the newest database professional would see with almost any table that is created. It will also get into some lesser known options for these indexes that can sometimes really improve performance. These include:
- Clustered Indexes
- Non-Clustered Indexes
Next, we will look at some of the more specialized index types. Some of these indexes can be created on regular tables and datatypes and some work only on specific datatypes. These include:
- ColumnStore Indexes
- Full Text Indexes
- XML Indexes
- Spatial Indexes
- Filtered Indexes
Finally, we’ll look at index maintenance and usage and provide some tools that you can use to make sure that your indexes are improving the performance of your database system.
- Index Fragmentation
- More Information on Indexes
NOTE: All the example code in each of the following sections was written and tested against the Adventureworks2014 sample database.
