Database Tools for SQL Server

Introduction to indexes in SQL Server

The text below is a part of a presentation I held about Indexes in SQL Server. Hopefully this can be useful for someone who wants a short introduction or repetition of indexes.

Indexes help out to find the data needed without having to search through all data. In short it does this by keeping different ordered lists of the data and references to related data.

There are two types of indexes, clustered and non-clustered. The clustered index is on the table itself. The columns in the index decide the order of the rows stored on disk. You can think of a clustered index as a dictionary, just a long list of ordered data.

A non-clustered index is instead like an index in a book. Every row in the non-clustered index has a reference to the clustered index. Just as the keywords in the index of a book have a reference to which pages it can be found on.

How to choose indexes
There are a lot of rules and hints for choosing good indexes. I will mention some of them here:

o Analyse WHERE clauses and JOIN criteria used on the table. The columns referenced are your best candidates to be added to indexes.

o Avoid columns with wide types and keep the number of columns in the indexes low. A wide index will require more disk space, which also means more work for SQL Server to read the index. The chance that the index is available in the database cache will also decrease by its bigger size.

o An index on a column with few possible values will not benefit performance. However, it could still be useful in an index with serveral columns in it.

o Consider the order of the columns in an index. The selected order should be equivalent to how you would order the conditions in a WHERE clause. The columns/conditions with the highest uniqueness first, and secondly the column with the smallest data type.

o Consider the pros and cons when choosing between a clustered and a non-clustered index.

• You can only have one clustered index per table. A good rule is to create the clustered index first.
• Keep the clustered index as small as possible. This is because every non-clustered index on the table contains the clustered index, to use as a reference to the main table.
• Do not create a clustered index on a frequently updated column. When it is updated every non-clustered index need to be updated as well.
• If you have a query that references columns both within and not within a non-clustered index, SQL Server need to lookup these data via the clustered index. This lookup costs a lot in performance, so a general rule is to make sure a non-clustered index is highly selective. I.e. the number of rows returned must not be too many.
• As default SQL Server creates a clustered index on an incremental field.

More indexing techniques
There are a couple of more indexing techniques that can be used to increase the effectiveness of your indexes. I will mention three of them which I find most important to know about. They are all related to the issue with looking up data not available in a non-clustered index.

Covering Indexes
A non-clustered index having all columns needed by a query is called a covering index. By adding all columns used in both the SELECT list and the WHERE/JOIN conditions you can avoid the need for accessing the main table. Obviously using this technique can lead to huge indexes that are expensive to maintain.

Covering Index with INCLUDE
Another way to make a covering index is to use the INCLUDE operator introduced in SQL2005. Instead of adding all columns to the index you include them unsorted together with the reference to the main table.

Using this technique the columns in the WHERE/JOIN conditions are still good candidates for the non-clustered index. The columns in the SELECT list could instead be added with the INCLUDE operator.

Index Intersection
Instead of creating wide covering indexes one can create several non-clustered indexes on different columns. SQL Server can then merge the result of all indexes to avoid a lookup of data in the main table.

You can look at the execution plan of your query to find out which indexes SQL Server uses and if a lookup of data in the main table was done.

Anders Svensson

Thanks for sharing your notes. I really enjoyed your presentation btw.

Stephen Jones

Thanks, this made my day!

Name Comment Send