Understanding SQL Indexes : Clustered vs Non-Clustered

SQL indexes is a very popular tool for improving query performance and reducing load on server. Suppose you are in a library looking for your favorite book. You can find what that book very fast if the books are neatly arranged by title or author. However, it would take ages to find it if the books were dispersed at random because you will have to scan the entire library.

Databases function similarly. Databases use something called SQL indexes to make data searches quicker and more effective.

Terms like Clustered and Non-Clustered Indexes may seem scary to someone who is unfamiliar with databases. But don’t worry, they’re much simpler to understand once you put them in plain, everyday language. We will help you with this in this articcle.

Let’s dissect it.

What are SQL Indexes in a Database?

For your data, an index functions similarly to a road map. It facilitates quicker information retrieval in the database without requiring a full table scan. Consider an index to be similar to the book’s back index: Rather than having you turn each page, it tells you exactly where to look.

In order to locate what you’re looking for, a database would need to search through every row of a table without indexes, which is extremely slow, particularly if the table contains millions of records!

Clustered vs Non-Clustered SQL Indexes: The Basics

Indexes are of two main types:

Let’s dive a little deeper into each one.

What is a Clustered SQL Index?

Data is physically sorted and stored on the disk in the same order as the index using a clustered index.

To put it simply:

• The clustered index is used to arrange the data in the table.
• Since the data cannot be physically arranged in two different ways simultaneously, there can only be one clustered index.

Example:

An example would be to make a clustered index on the “EmployeeID” column of a table of employees.
EmployeeID will now be used to store and sort the data in the table.

Because the database knows exactly where to go, querying by EmployeeID will always be incredibly quick. It’s like flipping straight to the correct contact in a sorted phone book.

Important Points:

• Data IS the clustered index.
• Typically, the primary key of a table is the clustered index by default.

What is a Non-Clustered SQL Index?

Think of Non-Clustered Index like a separate list that points to the actual data.

• The table’s data remains unchanged and unsorted.

• A pointer to the location of the complete data row is also stored in the non-clustered index, along with a copy of the indexed columns.

Example:

Let’s continue with the employee table and assume that you frequently search by “LastName.” On the “LastName” column, a non-clustered index can be made.

The database now has a shortcut to locate employees by their last names, even though the table itself isn’t sorted by LastName.

Key Points:

• A non-clustered index is a shortcut to the information.
• With SQL Server, a table can have as many as 999 non-clustered indexes.

Clustered vs Non-Clustered SQL Indexes: Visual Example

Imagine a Book:

• Clustered Index → The book’s pages are arranged according to a certain system, such as by chapter numbers.
  Because the pages are physically in the correct order, you turn them quickly.
• Non-Clustered Index → Topics and page numbers are listed in the book’s index at the back.
  When you search for a topic, it directs you to a particular page, which you then access.

When to Use Clustered vs Non-Clustered SQL Indexes

Important Things to Remember

• Only ONE clustered index is possible per table.

• Multiple non-clustered indexes can be allowed per table.

• Indexes do speed up SELECT queries but we should also note that they can slow down INSERT, UPDATE, DELETE because we need to update the index as well.