- SQL Server Blogs -

Difference Between Clustered and Non-Clustered Index in the SQL

Indexes are used to fasten the query process in the SQL server that results in high performance. They are quite similar to the textbook indexes. In a textbook, to open a particular chapter or topic, you first go to the index, and find the name of chapter or topic then jump to the desired page directly. Without indexes in the textbook, finding a specific topic in the textbook would be very slow.

The same concept is applied to the database indexes as well. If there are no indexes in the SQL then DBMS has to go through all the records in a table to retrieve the desired results. In this case, the process of scanning the desired records is extremely slow. On the other hand, when you create indexes, the DBMS goes to that index first and retrieves the corresponding record directly without wasting any time into other things.

There are two common types of indexes in the SQL server, these are the Clustered Index and the non-clustered index.

What are clustered indexes in the SQL Server?

Every table in the database has a natural order. If the order is random and it is not defined explicitly then the table is known as the heap here. Except for the few cases, heaps are not considered as a good programming practice because the SQL server has no meta-knowledge about how data is stored within a heap. SQL Server Curriculum If there is not a random heap, it means table data is stored in some order and the physical storage of data is defined by the clustered indexes in SQL. Every table can have only one clustered index because data can be stored in a single order only. It signifies that data in a table stored physically on the disk cannot have more than one order.

Example of Clustered Indexes

Take the example of “Student_info” table to understand the practical aspects of clustered indexing in the SQL server. This table contains “Roll_No” as the primary key and the clustered index is self-created here on the primary key that will sort the “student_info” table as per the “Roll_No”. In other words, clustered indexing works as the dictionary where sorting order is alphabetical and there is no need for defining any additional index page.

Input:
CREATE TABLE Student_info
(
ROLL_NO int(10) primary key,
NAME varchar(20),
DEPARTMENT varchar(20),
);
insert into Student_info values(1410110405, 'H Agarwal', 'CSE') 
insert into Student_info values(1410110404, 'S Samadder', 'CSE')
insert into Student_info values(1410110403, 'MD Irfan', 'CSE') 
SELECT * FROM Student_info 

Output for this query will be:

 

ROLL_NO NAME DEPARTMENT
1410110403 MD Irfan CSE
1410110404 S Samadder CSE
1410110405 H Agarwal CSE

If you want to define clustered indexes for other columns then first you should remove the primary key then you can remove the first index too. Keep in mind that defining a column as the primary key will make that column the clustered index of that table. To set any other column as the clustered index, we should remove the previous one with the following procedure:

Read: Microsoft SQL Server Developer Role: Job Responsibilities & Description
//Drop index
drop index table_name.index_name
//Create Clustered index index
create Clustered index IX_table_name_column_name 
             on table_name (column_name ASC) 

Table:Student_info

 

ROLL_NO NAME DEPARTMENT
1410110405 H Agarwal CSE
1410110404 S Samadder CSE
1410110403 MD Irfan CSE

Output: Index

NAME ROW_ADDRESS
H Agarwal 1
MD Irfan 3
S Samadder 2

What are the benefits of a clustered index?

  • Finding the desired data in the clustered index is a matter of knowing where to look in our alphabetical list of data.
  • If you need the output in the same order then it is stored in Presto, and there is no addition sorting required later.
  • It is usually fine putting the clustered index on the primary key of a table because primary key is generally the most used field for joins, where statements etc.
  • If you don’t want to put the primary key as the clustered index then drop the previous index and define it again as required.
  • One major benefit of the clustered index is that actions like insert and updates become faster because SQL doesn’t have to put data in sorted order any more.
  • Another common benefit of the clustered index is that we don’t have to include any additional data in our index. It is necessarily not true for other types of indexes like non-clustered indexes.
  • Pretend the clustered indexes like a phonebook where all contacts are arranged in alphabetical order and it is easy to find the desired one without performing any additional research.

What are nonclustered indexes in the SQL Server?

A nonclustered index contains the ordered data for the columns specified in that index with pointers that will guide where to find the rest of the data from that row. Unlike clustered indexes, nonclustered indexes are a two-step process where you should first the value of interest then go and look up the rest of the data of that row where it actually exists on the disk. SQL Server quiz The non-clustered index is an index structure separate from the data stored in the table that renders one or more selected columns. The non-clustered index is created to improve the performance of frequently used queries that are not managed well by clustered indexes. It is like a notebook where the index page is put separately at the beginning of the book.

Example of nonclustered indexes:

Input: 
CREATE TABLE Student_info
(
ROLL_NO int(10),
NAME varchar(20),
DEPARTMENT varchar(20),
);
insert into Student_info values(1410110405, 'H Agarwal', 'CSE') 
insert into Student_info values(1410110404, 'S Samadder', 'CSE')
insert into Student_info values(1410110403, 'MD Irfan', 'CSE')

SELECT * FROM Student_info 

Output for this query will be:

 

Read: SAS Tutorial Guide for Beginners
ROLL_NO NAME DEPARTMENT
1410110405 H Agarwal CSE
1410110404 S Samadder CSE
1410110403 MD Irfan CSE

Keep in mind that it is possible creating multiple nonclustered indexes for the database tables while the clustered index is exactly one for every database table. Here is how to define nonclustered indexes in the SQL server –

Input: 
//Create Non-Clustered index
create NonClustered index IX_table_name_column_name 
      on table_name (column_name ASC)  

Output: Table:Student_info

 

ROLL_NO NAME DEPARTMENT
1410110405 H Agarwal CSE
1410110404 S Samadder CSE
1410110403 MD Irfan CSE

  Input: create NonClustered index IX_Student_info_NAME on Student_info (NAME ASC) Output:

Index

NAME ROW_ADDRESS
H Agarwal 1
MD Irfan 3
S Samadder 2

What are the benefits of a nonclustered index?

  • We can define more than one nonclustered indexes on our tables as we want. It is really great creating a separate index for every column.
  • You get the speed and efficiency in data lookup but with the cost of losing the disk space by creating nonclustered indexes. Sometimes creation of unnecessary indexes may disturb the performance cycles badly, so test your changes closely.
  • Using non clustered indexes to find the desired data is preferable and computers are really good at using this process to look up the data.
  • Non clustered indexes improve the performance because you don’t have to go somewhere else to find the data but you lose storage space here by creating supplicate values as the part of your index.

Example Usage Scenarios

If there is an OLTP table that is used for transactional reads and writing new rows. The primary key is the major identifier here. So, what type of index you should use for the primary key here?

It is recommended using the clustered index here when the primary key is the major identifier for the table. If you store the data in the same format as arranged by the primary key then SQL can read data very quickly. free SQL Server demo If there is a query that you want to return most or all the columns from a table then what type of index is suitable in this case?

Read: MSBI Tutorial Guide for Beginner

The clustered index is suitable here because all columns are stored in the same order as sorted by the primary key in  the clustered indexing process.

There is a table whose values changes constantly and those updated values are used in Join and Where clauses. So, which type of indexing suits most here?

Nonclustered indexes are more suitable for tables whose values change frequently.

Final Words:

From the discussion, it is clear how clustered and nonclustered indexes are different.

  • There can be only one clustered index per table while nonclustered tables can have multiple indexes.
  • Clustered indexes sort data and don’t require any additional extra space while nonclustered indexes are stored in a separate table from the actual and demands for more storage space.
  • Clustered indexes are usually faster when compared to nonclustered indexes as they don’t demand the extra look space.

For more information on SQL indexing and its usage, join the SQL certification course online at JanBask Training and master the database concepts thoroughly.

SQL Tutorial Overview

    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews