What is the Difference Between Clustered and Nonclustered 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 to retrieve records from the database files easily and efficiently. It speeds up the query performance for the client applications that use the database. Moreover, there are two types of indexes as clustered and nonclustered index. Clustered index changes the order in which the data is stored in the disk. However, in a nonclustered index, the logical order of the index does not match the physical order of data stored in the 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 Nonclustered Index
     – Comparison of Key Differences

Key Terms

Clustered Index, Nonclustered Index

What is an Index

Assume a table that stores the details of the customer. It has three columns as first-name, last-name and contact number. If there are no indexes in the table, the data will be inserted into the free spaces in the storage without any particular order. If the user requires obtaining a specific customer’s phone number, it is necessary to search data from the beginning. Even though we get the data, it is necessary to continue until the end because there can 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 the data is physically stored. After table data is in the physical order, the DBMS creates index pages. They help to navigate easily to the required data. The entire structure with the base table data is known as a clustered index. The process of a query navigating through the clustered index tree to the base table data is called clustered index seek. There is one clustered index per table because it is impossible to physically arrange data in two different ways without using separate structures.

What is a Nonclustered Index

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

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

Difference Between Clustered and Nonclustered Index

Definition

A clustered index is a type of index where the table records are physically re-ordered 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 Indexes

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

Functionality

Clustered index does not store pointers to the actual data. However, non clustered index stores both the value and a pointer to the actual row that holds data. Hence, this is another difference between clustered and nonclustered index.

Order of Storing Data

Moreover, a cluster index determines the order of storing the data on the disk while a nonclustered index has no effect on the order of storing data on the disk.

Required Memory Space

In addition, a nonclustered index requires more memory space than a clustered index.

Speed

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 nonclustered 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 brief, a nonclustered index is slower and requires more space than a clustered index.

Reference:

1. Yadav, Durgaprasad. “Index in Sql Server.” LinkedIn SlideShare, 21 Nov. 2015, Available here.
2. Rahaman, Mahabubur. “Introduction of Sql Server Indexing.” LinkedIn SlideShare, 23 May 2015, Available here.

Image Courtesy:

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