Open In App

Difference Between Clustered and Non-Clustered Index

Last Updated : 23 May, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

In SQL Server, indexing plays a crucial role in enhancing data retrieval efficiency. Indexes are disk-based structures linked to tables or views, aiding in quicker row retrieval by organizing data in a structured manner. Two primary types of indexes in SQL Server are Clustered and Non-Clustered Indexes.

An index is a disk-based structure linked to a table or view that facilitates quicker row retrieval. A table or view’s table or view’s columns are used to create keys in an index. These keys are kept in a structure (B-tree) that enables SQL Server to quickly and effectively locate the row or rows that correspond to the key values.

Clustered Index 

A clustered index is created only when both the following conditions are satisfied:

  • The data or file, that you are moving into secondary memory should be in sequential or sorted order.
  • There should be a key value, meaning it can not have repeated values. 

Whenever you apply clustered indexing in a table, it will perform sorting in that table only. You can create only one clustered index in a table like a primary key. A clustered index is as same as a dictionary where the data is arranged in alphabetical order. 

In a clustered index, the index contains a pointer to block but not direct data. 

Example of Clustered Index

Example of Clustered Index

Example of Clustered Index 

If you apply the primary key to any column, then automatically it will become a clustered index. 

Create Table

Create table Student
( Roll_No int primary key,
Name varchar(50),
Gender varchar(30),
Mob_No bigint );

insert into Student
values (4, 'ankita', 'female', 9876543210 );

insert into Student
values (3, 'anita', 'female', 9675432890 );

insert into Student
values (5, 'mahima', 'female', 8976453201 );

In this example, Roll no is a primary key, it will automatically act as a clustered index. The output of this code will produce in increasing order of roll no. 

Output

Student table

Student table

You can have only one clustered index in one table, but you can have one clustered index on multiple columns, and that type of index is called a composite index. 

Here, the Roll_No column serves as the primary key, automatically becoming the clustered index. The output of querying this table will present data in ascending order of Roll_No.

Key Differences:

Only one clustered index is allowed per table.

The clustered index directly affects the physical ordering of data.

It offers faster retrieval but may slow down insert and update operations.

Non-Clustered Index

The non-Clustered Index is similar to the index of a book. The index of a book consists of a chapter name and page number, if you want to read any topic or chapter then you can directly go to that page by using the index of that book. No need to go through each and every page of a book. 

The data is stored in one place, and the index is stored in another place. Since the data and non-clustered index is stored separately, then you can have multiple non-clustered indexes in a table. 

In a non-clustered index, the index contains the pointer to data. 

Non clustered index

Example of Non Clustered Index

Example of Non-Clustered Index 

The given code creates a table “Student” with columns “Roll_No”, “Name”, “Gender”, and “Mob_No”. The primary key is defined on the “Roll_No” column. Three rows are inserted into the “Student” table with different values for the columns. Finally, a nonclustered index “NIX_FTE_Name” is created on the “Name” column in ascending order.

The “CREATE TABLE” statement is used to create a new table “Student” with four columns “Roll_No”, “Name”, “Gender”, and “Mob_No”. The “Roll_No” column is defined as the primary key of the table.

The “INSERT INTO” statements are used to insert three rows of data into the “Student” table. Each row contains values for all the columns of the table. The first row has a roll number of 4, the name of “Afzal”, the gender of “male”, and a mobile number of 9876543210. The second row has a roll number of 3, the name of “Sudhir”, the gender of “male”, and a mobile number of 9675432890. The third row has a roll number of 5, name of “zoya”, the gender of “female”, and a mobile number of 8976453201.

Query:

Create table Student
( Roll_No int primary key,
Name varchar(50),
Gender varchar(30),
Mob_No bigint );

insert into Student
values (4, 'afzal', 'male', 9876543210 );

insert into Student
values (3, 'sudhir', 'male', 9675432890 );

insert into Student
values (5, 'zoya', 'female', 8976453201 );

create nonclustered index NIX_FTE_Name
on Student (Name ASC);

Here, roll no is a primary key, hence there is automatically a clustered index. If we want to apply a non-clustered index in the NAME column (in ascending order), then a new table will be created for that column. 

Output:

student table

Student table

The row address is used because, if someone wants to search the data for Sudhir, then by using the row address he/she will directly go to that row address and can fetch the data directly.

In this example, the Name column is indexed in ascending order, allowing for efficient retrieval based on names.

Key Differences:

Multiple non-clustered indexes are allowed per table.

Non-clustered indexes store data pointers, not the data itself.

They offer flexibility but may result in slower retrieval compared to clustered indexes.

Difference Between Clustered and Non-Clustered Index 

CLUSTERED INDEX NON-CLUSTERED INDEX
A clustered index is faster. A non-clustered index is slower.
The clustered index requires less memory for operations. A non-Clustered index requires more memory for operations.
In a clustered index, the clustered index is the main data. In the Non-Clustered index, the index is the copy of data.
A table can have only one clustered index. A table can have multiple non-clustered indexes.
The clustered index has the inherent ability to store data on the disk. A non-Clustered index does not have the inherent ability to store data on the disk.
Clustered index store pointers to block not data. The non-clustered index stores both the value and a pointer to the actual row that holds the data
In Clustered index leaf nodes are actual data itself. In Non-Clustered index leaf nodes are not the actual data itself rather they only contain included columns.
In a Clustered index, Clustered key defines the order of data within a table. In a Non-Clustered index, the index key defines the order of data within the index.
A Clustered index is a type of index in which table records are physically reordered to match the index. A Non-Clustered index 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 the disk.
The size of The primary clustered index is large. The size of the non-clustered index is compared relativelyThe composite is smaller.
Primary Keys of the table by default are clustered indexes. The composite key when used with unique constraints of the table act as the non-clustered index.

Conclusion

In Conclusion, the decision between clustered and non-clustered indexes primarily depends on the particular requirements of your database.Choosing between clustered and non-clustered indexes depends on the specific requirements of your database:

Clustered indexes are ideal for range queries and static data.

Non-clustered indexes are suitable for optimizing various query types and dynamic data environments.

Understanding these indexing techniques empowers database administrators to optimize data retrieval and performance effectively.

Index Clustering

  • Perfect for tables where range queries, in particular, place a high value on data retrieval efficiency.
  • Ideal for tables with few updates or relatively static data because moving data around can slow down insert and update operations.

Non-Clustered index

  • allows for the optimization of various query types without changing the data’s physical order on disk.
  • Ideal for tables where data changes often since inserts and updates are typically quicker than with clustered indexes.


Previous Article
Next Article

Similar Reads

SQL Queries on Clustered and Non-Clustered Indexes
Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, the SQL server will do the exact same thing you do when searching for content in a book by checking the index page. In the same way, a table's index allows us to locate the
5 min read
Difference between Inverted Index and Forward Index
Inverted Index It is a data structure that stores mapping from words to documents or set of documents i.e. directs you from word to document.Steps to build Inverted index are:Fetch the document and gather all the words.Check for each word, if it is present then add reference of document to index else create new entry in index for that word.Repeat a
2 min read
Difference between Cost Performance Index (CPI) and Schedule Performance Index (SPI)
Cost Performance Index (CPI): Cost Performance Index (CPI) is the measure of the cost efficiency of project. It is expressed as a ratio of earned value to actual cost. Schedule Performance Index (SPI): Schedule Performance Index (SPI) is the measure of schedule efficiency of the project. It is expressed as the ratio of earned value to planned value
2 min read
Difference between index.ejs and index.html
What is HTML? HTML (Hypertext Markup Language) is used to design the structure of a web page and its content. HTML is actually not technically programming languages like C++, JAVA, or python. It is a markup language, and it ultimately gives declarative instructions to the browser. When a web page is loaded, the browser first reads the HTML and cons
4 min read
Difference Between Dense Index and Sparse Index in DBMS
Indexing is a technique in DBMS that is used to optimize the performance of a database by reducing the number of disk access required. An index is a type of data structure. With the help of an index, we can locate and access data in database tables faster. The dense index and Sparse index are two different approaches to organizing and accessing dat
3 min read
Git - Difference Between HEAD, Working Tree and Index
Git as a version-control-system manages and manipulates three trees in its normal operation: HEAD: Last commit snapshot, next parentIndex: Proposed next commit snapshot Working Directory: SandboxHead HEAD is the pointer to the current branch reference, which is in turn a pointer to the last commit made on that branch. That means HEAD will be the pa
3 min read
Difference between Adaptive and Non-Adaptive Routing algorithms
Prerequisite - Classification of Routing Algorithms 1. Adaptive Routing algorithm: Adaptive routing algorithm is also called a dynamic routing algorithm. In this algorithm, the routing decisions are made based on network traffic and topology. The parameters which are used in adaptive routing algorithms are distance, hop, estimated transit time and
4 min read
Difference between Volatile Memory and Non-Volatile Memory
Volatile Memory: It is the memory hardware that fetches/stores data at a high-speed. It is also referred as temporary memory. The data within the volatile memory is stored till the system is capable of, but once the system is turned off the data within the volatile memory is deleted automatically. RAM (Random Access Memory) and Cache Memory are som
3 min read
Difference between Preemptive Priority based and Non-preemptive Priority based CPU scheduling algorithms
Prerequisite - CPU Scheduling Priority Scheduling : In priority scheduling, each process has a priority which is an integer value assigned to it. The smallest integer is considered as the highest priority and the largest integer is considered as the lowest priority. The process with the highest priority gets the CPU first. In rare systems, the larg
4 min read
Difference between Primordial and Non-Primordial Threads
When the operating system starts a new process, there is only one thread. This is the thread that will enter the application's native main function, which may then spawn other threads. The "primordial thread" is the first single thread; it isn't a formal noun, but rather a word whose meaning should be deduced from context.It's understandable that a
5 min read