In SQL Server, an index is used to speed the retrieval of data from a table. Indexes are built from one or more columns in the table, and SQL Server uses these Indexes to look up and retrieve data quickly and efficiently. In order to effectively use Indexes in SQL Server, it’s important to understand what the different types of Indexes are. In this Index Basics post, we will cover the basics about Nonclustered Indexes by trying to answer a few common questions.
What is a Nonclustered Index?
At a very basic level, a nonclustered index is just a smaller subset of your table’s data which contains pointers back to the actual data rows in your table. This subset of data can be scanned faster than a table, and is used to help SQL Server find data more quickly than searching through a table. In some cases your entire query can be answered by the data in the nonclustered index and SQL Server doesn’t actually have to access the table. Other times SQL Server will use the nonclustered index to quickly locate and retrieve the data rows it needs from the table. Either way, searching the index is usually much faster than searching the table because the index contains fewer columns and its rows are stored in a sorted order.
What is the difference between Clustered and Nonclustered Indexes?
A clustered index tells SQL Server how to physically sort and store a table’s data. It is considered to be the most important index you can have on a table, and Microsoft states that “With few exceptions, every table should have a clustered index”. Since the data rows in a table can only be stored in one order, there can only be one clustered index per table. Because everything will use the clustered index to find the data in your table, it is the most replicated data structure in your SQL Server database. For more details on clustered indexes, see my Index Basics: Clustered Indexes post.
Nonclustered indexes are created to improve the performance of queries. A nonclustered index has no effect on how the data in the table will be stored, it exists separately from the table. All of your nonclustered indexes will contain the clustered index (or Row ID if your table does not have a clustered index) as a pointer back to the data rows in your table. You can create multiple nonclustered indexes on a table, and these are usually the most common indexes in your database. In fact, when using the CREATE INDEX statement in SQL Server, it will default to a nonclustered index unless otherwise specified. When you hear the generic term “index” in SQL Server, it is usually referring to a nonclustered index.
How many Nonclustered Indexes can my table have?
A lot. Sometimes too many, in fact. SQL Server allows multiple nonclustered indexes on a table, how many depends on the version of SQL Server you’re using. SQL Server 2005 allows 249 Nonclustered Indexes per table, while SQL Server 2008 and above (currently 2016 at the time of this writing) allows 999 Nonclustered Indexes per table. That’s a lot of indexes.
This would be a good time to point out that just because you CAN create that many indexes, does not always mean you SHOULD. Databases that contain primarily read-only data can benefit from many nonclustered indexes, while databases that contain heavily updated tables should avoid over-indexing. As data in your table is added and modified, SQL Server will also modify the data in all of that table’s indexes accordingly. A large number of indexes on a table affects the performance of INSERT, UPDATE, DELETE, and MERGE statements because SQL Server has to do more work to keep those indexes accurate. So, if you have 999 indexes on a table, and you insert a new record, that’s 1000 inserts that SQL Server has to make. 1 insert for the actual table, and then 999 for each of the indexes.
How do I know which columns to use in a Nonclustered Index?
That’s the million dollar question, and unfortunately there is no “one size fits all” answer. The columns that you choose for your index are going to depend on the query or queries that you are creating the index for. A good rule of thumb is to index columns referenced in the WHERE clause and columns used in JOIN or GROUP BY clauses in your query.
Here are a few Nonclustered Indexing Tips:
- Put a nonclustered index on each foreign key column in your table, this will aid in speeding up query joins back to the parent table.
- Indexes should be kept narrow, with as few columns as possible. However, nonclustered indexes on single columns are rarely effective (except in the case of foreign key columns mentioned above).
- Try to avoid creating duplicate indexes – If you have two (or more) indexes over the exact same columns, get rid of all but one of them. Also, if you have multiple indexes that only have slight variations of each other, try to consolidate them into one index. It is better to have fewer, more effective indexes than it is to have many indexes that may not even be used.
- A column with few unique values is seldom a good candidate to be indexed. More unique data in the index makes the index more useful.
- Avoid over-indexing heavily updated tables.
- The order of the columns in the index is important. As stated in Microsoft’s in SQL Server Index Design Guide, “The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.”
The Bottom Line…
Nonclustered indexes are a key factor in improving query performance. The purpose of a nonclustered index is to support a query or queries. It contains a subset of your table’s data, which SQL Server can scan faster, and in turn locate and return the data to your query faster. Tables with low update requirements can benefit from many nonclustered indexes, while tables that are heavily updated should avoid over-indexing as that can affect the performance of INSERT, UPDATE, DELETE, and MERGE statements. Choosing the right nonclustered indexes, and the number of those indexes, can sometimes become a balancing act between query speed and update cost.
In my next article, I’ll discuss some of the variations of nonclustered indexes, such as filtered indexes, covering indexes, included columns, sorting and more!
Resources:
For a more detailed explanation of nonclustered indexes, see the below resources:
SQL Server Index Design Guide (Technet)
Clustered and Nonclustered Indexes Described (MSDN)
Create Nonclustered Indexes (MSDN)
Understanding SQL Server Indexing
SQL Server Index Basics
Pingback: The Power Of Proper Indexing - SQL Nuggets