What is a clustered index? What is a non-clustered index? And how are they different?
They are two types of indexes in SQL Server.
Learn what they are and see some examples in this guide.
What is an Index?
An index is a database object that stores specific values and a reference to rows in the database table that they refer to.
It’s similar to how a library organises books. The books are stored on shelves and are organised in a particular order, and have an ID number or some kind of reference number (the Dewey Decimal System, for example).
A database index works in the same way. If you know the ID number you can find the book.
The purpose of an index is to speed up the queries on the database.
In SQL Server, there are two types of indexes: clustered and non-clustered.
What is a Clustered Index?
A clustered index is a type of index that defines how data is stored for the table. The data in a table is stored on a disk on the server, and a clustered index specifies that the data in the table is stored in the same order as the data in the index.
Or, more specifically, the leaf level (the lowest level) of the clustered index is the table rows. This means the clustered index could be described as being the table.
Using the library example, the clustered index would be the Dewey Decimal System ID number. Let’s say you wanted to find the book with the ID of 021.41092. You would first look for the bookshelf that had the books ranging from 000-025 by reading the signs at the end of the rows.
You would then scan the shelves, looking for books that were close to the one you were looking for, until you find the book with the ID you were looking for: 021.41092.
This is how a clustered index works. The books are stored in the same order as their IDs (which is the indexed value), and a clustered index is stored in the same way as the lowest level values of the index.
Why is this important? Because, if the data is stored in the same order in the clustered index and the table, it’s faster for the database to find the data in the table.
The only drawback is that there can only be one clustered index on a table. This is because the clustered index defines how the data is stored. There can’t be two ways to store the data in the table. Or, using the library example, there can’t be two ways to organise the same books on the bookshelves.
What is a Non-Clustered Index?
A non-clustered index is a type of index that stores a separate list that refers to the rows in the table. The list is separate and it does not define how the records are stored in the table.
Using our library example, let’s say you were looking for a book but didn’t know what the Dewey Decimal System ID number was, but you knew the author or the title. Libraries used to have a set of drawers, like a filing cabinet, called a Card Catalog. These had a set of cards, one for each book, with the book and the ID number on it. These cards were sorted in a specific order, such as alphabetically by author.
So, if you knew the author, you could look up the author in this card catalogue, find the book ID number, then walk to that spot on the shelf and get the book.
The benefit of a non-clustered index is that there can be more than one on a table, as they don’t define how the data is stored in the tables.
Using the library example, you could create a copy of all of the cards in the card catalogue and sort them alphabetically by title. This would be a second “non clustered index”. It would help if you were searching by title, instead of the author.
The more non-clustered indexes you have, the easier it will be to find records (e.g. finding books by publisher or publication date). But it slows down the process to insert or update records.
Using the library example, if you add a new book, you would have to create one card for each catalogue and put it in the right place.
What is the Difference Between a Clustered and Non-Clustered Index?
We’ve learned what a clustered and a non-clustered index is. What are the differences between them?
|Impacts table storage||Yes||No|
|Number allowed for a table||1||Multiple|
Let’s take a look at some examples of creating indexes in SQL Server.
Example: Clustered Index
To create a clustered index in SQL Server, we can use the CREATE INDEX command:
CREATE CLUSTERED INDEX index_name ON table (column);
An example would be:
CREATE CLUSTERED INDEX ix_cust_id ON customer (cust_id);
This will create a clustered index on the cust_id column in the customer table. The index is called ix_cust_id.
Because there can only be one clustered index on a table, we’ll get an error if we try to create a second clustered index (even if it’s on a different column).
CREATE CLUSTERED INDEX ix_cust_id2 ON customer (cust_name);
Cannot create more than one clustered index on table ‘customer’. Drop the existing clustered index ‘ix_cust_id’ before creating another.
Example: Non-Clustered Index
Creating a non-clustered index in SQL Server is similar:
CREATE [NONCLUSTERED] INDEX index_name ON table (column);
The NONCLUSTERED keyword is optional. You can include it to make it clear that the index is non-clustered, or leave it out.
An example would be:
CREATE INDEX ix_cust_name ON customer (cust_name);
This index is successful, even if there is already a clustered index on the table.
You can create an index using the optional NONCLUSTERED keyword as well, which does the same thing:
CREATE NONCLUSTERED INDEX ix_cust_name2 ON customer (cust_name);
So that’s how you can create a non-clustered index in SQL Server.
A clustered index defines how the table data is stored on the disk, and there can only be one for a table. A non-clustered index is separate to how data is stored, and there can be many of them on a table. Both are useful ways of improving the performance of your SQL queries.