Clustered vs Non-clustered Index: Key Differences with Example

What is an Index?

An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view. This key helps a Database like Oracle, SQL Server, MySQL, etc. to find the row associated with key values quickly.

Two types of Indexes are:

• Clustered Index
• Non-Clustered Index

In this tutorial, you will learn:

• What is an Index?
• What is a Clustered index?
• What is Non-clustered index?
• Characteristic of Clustered Index
• Characteristics of Non-clustered Indexes
• An example of a clustered index
• An example of a non-clustered index
• Differences between Clustered Index and NonClustered Index
• Advantages of Clustered Index
• Advantages of Non-clustered index
• Disadvantages of Clustered Index
• Disadvantages of Non-clustered index

What is a Clustered index?

Cluster index is a type of index which sorts the data rows in the table on their key values. In the Database, there is only one clustered index per table.

A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column.

What is Non-clustered index?

A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places.

For example, a book can have more than one index, one at the beginning which displays the contents of a book unit wise while the second index shows the index of terms in alphabetical order.

A non-clustering index is defined in the non-ordering field of the table. This type of indexing method helps you to improve the performance of queries that use keys which are not assigned as a primary key. A non-clustered index allows you to add a unique key for a table.

Characteristic of Clustered Index

• Default and sorted data storage
• Use just one or more than one columns for an index
• Helps you to store Data and index together
• Fragmentation
• Operations
• Clustered index scan and index seek
• Key Lookup

Characteristics of Non-clustered Indexes

• Store key values only
• Pointers to Heap/Clustered Index rows
• Allows Secondary data access
• Bridge to the data
• Operations of Index Scan and Index Seek
• You can create a nonclustered index for a table or view
• Every index row in the nonclustered index stores the nonclustered key value and a row locator

An example of a clustered index

In the example below, SalesOrderDetailID is the clustered index. Sample query to retrieve data

SELECT CarrierTrackingNumber, UnitPrice
FROM SalesData
WHERE SalesOrderDetailID = 6

An example of a non-clustered index

In the below example, a non-clusted index is created on OrderQty and ProductID as follows

CREATE INDEX myIndex ON
SalesData (ProductID, OrderQty)

The following query will be retrieved faster compared to the clustered index.

SELECT Product ID, OrderQty
FROM SalesData
WHERE ProductID = 714

Differences between Clustered Index and NonClustered Index

Advantages of Clustered Index

The pros/benefits of the clustered index are:

• Clustered indexes are an ideal option for range or group by with max, min, count type queries
• In this type of index, a search can go straight to a specific point in data so that you can keep reading sequentially from there.
• Clustered index method uses location mechanism to locate index entry at the start of a range.
• It is an effective method for range searches when a range of search key values is requested.
• Helps you to minimize page transfers and maximize the cache hits.

Advantages of Non-clustered index

Pros of using non-clustered index are:

• A non-clustering index helps you to retrieves data quickly from the database table.
• Helps you to avoid the overhead cost associated with the clustered index
• A table may have multiple non-clustered indexes in RDBMS. So, it can be used to create more than one index.

Disadvantages of Clustered Index

Here, are cons/drawbacks of using clustered index:

• Lots of inserts in non-sequential order
• A clustered index creates lots of constant page splits, which includes data page as well as index pages.
• Extra work for SQL for inserts, updates, and deletes.
• A clustered index takes longer time to update records when the fields in the clustered index are changed.
• The leaf nodes mostly contain data pages in the clustered index.

Disadvantages of Non-clustered index

Here, are cons/drawbacks of using non-clustered index:

• A non-clustered index helps you to stores data in a logical order but does not allow to sort data rows physically.
• Lookup process on non-clustered index becomes costly.
• Every time the clustering key is updated, a corresponding update is required on the non-clustered index as it stores the clustering key.

Summary

• An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view.
• Cluster index is a type of index which sorts the data rows in the table on their key values.
• A Non-clustered index stores the data at one location and indices at some other location
• Clustered is the default index
• The main benefit of a non-clustering index helps you to retrieves data quickly from the database table.
• The main benefit of the Clustered index method is that it uses location mechanism to locate index entry at the start of a range.
• In Clustered Index you can sort the records and store clustered index physically in memory as per the order while In non-clustered index helps you to creates a logical order for data rows and uses pointers for physical data files.
• The main drawback of a clustered index is that it creates lots of constant page splits, which includes data page as well as index pages.