What is Difference between Clustered vs Non-Clustered Indexes in a table? [SQL Interview Question]

Hello guys, what is he difference between Clustered and Non-clustered index in a relational database is one of the most popular SQL interview questions almost as popular as the primary key vs unique key, the difference between truncate and delete, and correlated vs noncorrelated subqueries. Indexes are a very important concept, it makes your queries run fast and if you compare a SELECT query which uses an indexed column to one who doesn't you will see a big difference in performance.

There can be two kinds of indexes in relational databases Clustered and Non-clustered indexes. 

A clustered index determines the physical sorting order of rows in a table similar to entries on yellow pages which are sorted in alphabetical order.

Suppose you have a table Employee, which contains emp_id as primary key then a clustered index which is created on a primary key will sort the Employee table as per emp_id. 

That was a brief introduction of what is clustered index in SQL.

On another hand, the Non-Clustered index involves one extra step which points to the physical location of the record. In this SQL Interview question, we will see some more differences between clustered and non-clustered indexes in point format.

Most of the things we discuss here will make more sense if you have a basic understanding of what is n index and how they work to understand that take a look at this diagram which shows how both clustered and non-clustered index work in SQL. 

You can see that in case of clustered index :

• how data is physically organized by the primary key
• Data pages contain all column data
• Data is sorted by EmployeeID
• Demonstrates direct access to full records

And, in case of non clustered index,:

• how you can create an index on Department column
• Contains pointers back to primary key
• Demonstrates how it references the clustered index
• Multiple department entries point to different IDs

Now that we have basic understanding of both type of indexes in a table or database, time to see the differences. 

Difference between Clustered and Non Clustered Indexes in SQL

In the last section, we have seen What is Clustered and Nonclustered indexes and How they resolve the physical location of the record. Let's see some more differences between them to answer this SQL interview question :

1.Physical Sorting

 A Clustered Index physically sorts all rows while Nonclustered Index doesn't.

2. Count

In SQL, one table can only have one Clustered Index but there is no such restriction on the NonClustered Index.

3. Primary Key

In many relational databases, the Clustered Index is automatically created on the primary key column.

4. Performance

Clustered Index can improve the performance of data retrieval while a non-clustered index should be created on columns that are used in the join, where, and order by clause. You can further see the SQL Performance Explained by Markus Winand to learn more about how to index work in different databases like MySQL, SQL Server, and Oracle.

Anyway, here are some key differences between a clustered index and a non-clustered index in the tabular format:

That's all on the difference between Clustered vs Non-clustered Index in SQL. An index is very critical to query performance but too many indexes can actually harm the performance of INSERT and UPDATE queries because these require rearrangement of indexes which can be time-consuming based on the size of the table or number of records on the table.

Always create Indexes on columns that are frequently used in a SELECT query because there is a huge performance difference between an indexed and non-indexed column in SQL queries, though you should be careful with creating too many indexes because they will make the update and delete statement slow.

An index is maintained in separate places and costs to update the index tree are directly proportional to the number of indexes you have. If you have indexed in all columns of the database then obviously, you have to re-arrange most of those index trees whenever you update or delete a row.


Other database and SQL articles you may like
What are the ACID properties of database transactions?
10 frequently used MySQL commands for beginners
Top 15 Linux command interview questions with answers

P. S. - If you are looking for online training/courses to learn SQL from scratch, I suggest you join these best SQL and Database courses. It's one of the best resources to learn SQL fundamentals like join, subquery, aggregate functions, window functions, grouping data, advanced filtering, and SQL query optimization.