What is the difference between clustered and non-clustered index?

The main difference between clustered and nonclustered index is that there is only one clustered index per table while there are multiple nonclustered indexes per table.

First of all, indexing is a technique for retrieving records from database files easily and efficiently. Speeds up query performance for client applications that use the database. Also, there are two types of indexes as clustered and non-clustered index. The clustered index changes the order in which data is stored on disk. However, in a nonclustered index, the logical order of the index does not match the physical order of the data stored on disk.

Key Areas Covered

1. What is an index?
     – Definition, Functionality
2. What is a clustered index?
     – Definition, Functionality
3. What is a nonclustered index?
     – Definition, Functionality
4. What is the difference between clustered and non-clustered index?
     – Comparison of key differences

Key terms

Clustered index, non-clustered index

What is an index?

Let’s assume a table that stores customer details. It has three columns like first name, last name and contact number. If there are no indexes on the table, the data will be inserted into the free spaces in storage in no particular order. If the user requires to obtain the phone number of a specific customer, it is necessary to search for data from the beginning. Although we get the data, it is necessary to continue to the end because there may be matching data at the end. Indexes are useful in these situations.

What is a clustered index?

A clustered index changes the order in which data is physically stored. Once the table data is in physical order, the DBMS creates the index pages. They help to easily navigate to the required data. The entire structure with the base table data is known as a clustered index. The process of a query that navigates through the clustered index tree to the base table data is called a clustered index lookup. There is a clustered index per table because it is impossible to physically organize the data in two different ways without using separate structures.

What is a nonclustered index?

A nonclustered index does not order the physical data in the table. The index and the table are stored in different places. Also, there are pointers or references to the data in the table. It is possible to store data in any order since that order is independent of the data in the base table. Therefore, there can be more than one nonclustered index per table. The data in the base table is on the heap while the references are the row identifiers.

When executing a query on a column, the database first goes to the index and looks up the address of the corresponding row in the table. Then it goes to the row address and looks for other column values. Therefore, nonclustered indexes are slower than clustered indexes. When there is a unique key defined on the table, a nonclustered index will be created automatically.

Difference Between Clustered and Nonclustered Index

Definition

A clustered index is a type of index in which the records in the table are physically reordered to match the index. A nonclustered index, on the other hand, is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. These definitions explain the difference between clustered and nonclustered index.

Number of indices

The number of indexes per table is an important difference between clustered and nonclustered index. A table can have a single clustered index, but multiple nonclustered indexes.

functionality

The clustered index does not store pointers to the actual data. However, the nonclustered index stores both the value and a pointer to the actual row that contains the data. Thus, this is another difference between clustered and nonclustered index.

Data storage order

Also, a clustered index determines the storage order of data on disk, while a nonclustered index has no effect on the storage order of data on disk.

memory space required

Also, a nonclustered index requires more memory space than a clustered index.

Velocity

Speed ​​is another difference between clustered and nonclustered index. Nonclustered indexes are slower than clustered indexes.

conclusion

There are two types of indexes as clustered and non-clustered index. The difference between clustered and nonclustered index is that there is only one clustered index per table while there are multiple nonclustered indexes per table. In short, a nonclustered index is slower and requires more space than a clustered index.

Reference:

1. Yadav, Durgaprasad. “Index on SQL Server”. LinkedIn SlideShare, November 21, 2015, available here.
2. Rahaman, Mahabubur. “Introduction to Sql Server Indexing”. LinkedIn SlideShare, May 23, 2015, Available here.

Courtesy image:

1.”Databases logo” By Borjasotomayor at English Wikibooks (CC BY-SA 3.0) via Commons Wikimedia