What is the Difference between Clustered and Non-clustered Index?

Nowadays, we use databases to store records, and to fetch records much more efficiently, we use indexes. The index is a unique key made up of one or more columns. There are two types of indexes:

1. Clustered Index
2. Non-Clustered Index

Clustered Index

A clustered index is an index that specifies the physical arrangement of a database's table records. There can only be one clustered index per table since there can only be one method that records are physically maintained in a database table. It stores the records in sorted order.

Non-Clustered Index

A non-clustered index is an index that doesn't specify the physical arrangement of the records maintained in the database’s table. The Non-Clustered Indexes are stored in a different table. Therefore, as they are maintained in a different table, there can be numerous non-clustered indexes that can be created for a single table.

Characteristics of the Clustered Index

The characteristics of a clustered index are as follows:

• Default Indexing Methodology
• can use a single or more than one column for indexing.
• Indexes are stored in the same table as actual records.
• It supports fragmentation functionality.
• Supports Scanning and Seeking using Clustered Indexes.
• It supports key lookup functionality.

Characteristics of the Non-Clustered Index

The characteristics of the non-clustered index are as follows:

• Table data is stored in the form of key-value pairs.
• Tables or Views can have indexes created for them.
• It contains pointers to clustered index records.
• Supports Index Scanning and Seeking Functionalities.
• It provides secondary access to records.
• A non-clustered key-value pair and a row identifier are stored in each index row of the non-clustered index.

Examples of Clustered and Non-Clustered Index

Clustered Index:

For Example, While Fetching records of an employee in an organization, we can directly access the records of the employee using his unique employee Id.

Query:

Non-clustered Index:

For example, while fetching the records of a product in a city, we can create an index using pincode and product id to fetch the records of each product efficiently and faster.

Index Creation Query:

Query:

Differences between Clustered and Non-clustered Index

Creating Clustered index

Clustered indexes are automatically created by the database for any table. Therefore, to create a new clustered index, we first need to delete the old clustered index. After deleting the index, we use the syntax given below to create a new clustered index.

Syntax:

Example:

Explanation: The above query will create a clustered index on the Emp table using the column name, which will make the records inserted in ascending order of the name by default.

Creating a Non-Clustered Index

As Non-Clustered Indexes are maintained in different tables, they can be numerous. So we don't need to delete previously created indexes. The syntax for creating a Non-Clustered Index is similar to the syntax for a clustered index except for the use of non-clustered Words. We can create a non-clustered index using the syntax given below:

Syntax:

Example:

Explanation: The above query will create a NONCLUSTERED Index on the Emp table using the column name, which will make the records inserted in ascending order of the name.

When to Use a Clustered or Non-Clustered Index?

• A Clustered Index should be used when we need to specify the physical order of the records inserted into a table. It is more efficient in time, i.e., faster than a non-clustered index.
• A Non-Clustered Index should be used when we need to logically maintain a sequence of data without altering the physical sequence or several indexes are needed to maintain the table. It is less efficient in time, i.e., slower than a clustered index, and also stores indexes in a different table.

Advantages of Clustered and Non-Clustered Index

Clustered Index

• For range or group queries that are generally used with max, min, or count functions, clustered indexes are the best solution.
• In a clustered index, a pointer can directly go to a certain place in the data so that you can continue reading in order from there.
• The Clustered Index helps you increase cache visits and decrease page transfers.
• Clustered indexes help in locating the index entry at the beginning of a range using a location mechanism.

Non-Clustered Index

• Non-Clustered Indexing aids you in avoiding the administrative costs related to clustered indexes
• In RDBMS, a table may contain many non-clustered indexes. Therefore, numerous indexes may be created using it.
• You may easily access information from the table with the use of a non-clustering index.

Disadvantages of Clustered and Non-Clustered Index

Clustered Index

• Numerous constant page splits, including data pages and index pages, are produced by clustered indexes.
• In Clustered Indexes, SQL queries like insert, delete, and update take more execution time and system resources.
• Most of the records in the clustered index are located in the leaf nodes.

Non-Clustered Index

• A non-clustered index allows you to store data in a logical order. A non-clustered index does not allow you to physically sort data records.
• The cost of a lookup on a non-clustered index increases.
• The non-clustered index must also be updated if the clustering key is altered because it includes the clustering key.

See Also

To learn more about indexes in the database, please go through the below link.

• Indexing in DBMS