Clustered Vs Non-Clustered Index – Explained With Example

A Clustered Index in SQL Server is a type of index that sorts and stores the data rows in the table based on their key values. There can be only one clustered index per table as the database can be sorted in only one way.

A Non-clustered Index in SQL Server, on the other hand, stores the data and indices at different locations. This means that it does not store the physical data inside the table.

In this post you’ll learn about, in depth, the key differences between clustered and non-clustered index with example.

But before that, let’s have a quick glance at what an index in SQL Server actually is in the first place…..

What is an Index in SQL Server

An Index is a database object that helps you find rows with unique column values quickly.

A simple way to understand an Index is through an example of an ‘index’ of a book. Suppose you are asked to open a specific chapter in the book. What you’ll do next, I hope, is open the index page of that book, locate the chapter by its name or number, take a glace at the page number stated adjacent to the chapter, and then directly jump to that page.

If it were not for the index page of your book, you would’ve to slog through each page, one by one, until you arrived at the specified chapter.
   
Similarly in SQL Server, if you don’t employ index in your database, SQL Server – when looking for a particular data item – will begin with the first row, and then scan through the entire table to find the specified rows. The time cost will increase with the size of the database. 

What is a Clustered Index

To understand clustered index, let’s get back to our book example once again. Let’s say that you are given a phone book, and you are asked to look for all the information about a guy named Jared Young. So, you look up his information by his first and last name- “Jared, Young” – and find all the information you wanted like number and address.

Here, the first and last name are the keys for clustered index.

And that’s why we define clustered index as:

A type of index that sorts the rows in the table based on their key values. And so, it defines the way in which data is physically stored in a table. There can only be one clustered index for a particular table, always.

What is a Non-Clustered Index

A Non-Clustered Index in contrast is a type of index that has a separate structure than the data rows. It stores the data at one location and indices at another. Furthermore, this index holds the pointers to the location of that data.

In terms of our ‘books’ example, suppose you also had to find people based on their address. A non-clustered index can have multiple indices- one which orders the content of the book according to the first name and the last name, and the second one- which orders the content by their addresses.

Key Differences Between Clustered and Non-Clustered Index in SQL Server

Based on what we have covered till now in this Clustered Vs Non-clustered indexes guide, the major differences between the two types of indices are:

Clustered IndexNon-Clustered Index
It is faster.It is slower when compared to clustered index.
A table can only have 1 clustered index. A table can have multiple non- clustered index.
Very large file size.Smaller size compared to non-clustered index.
In clustered index, the order of data is defined by clustered key.             
Here, the clustered key defines order of data within the index.

NOTE: Also, sometimes due to various kinds of errors in SQL server, these indexes might get corrupted. To resolve these errors, you can use the SysTools SQL recovery tool which will recover your files efficiently.

Key Takeaways

And that is it.

In this post, we began with the definition of what an Index is. Then you learned about what clustered and non-clustered index are and also covered the major differences between them.

I hope this article helped you understand clearly the differences between clustered and non-clustered index in SQL Server.

This is a guest post by Jared Young.

Leave a Comment