Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

Difference Between Clustered and Non-Clustered Index in the SQL



Introduction

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

There are two common types of indexes in the SQL server, these are the Clustered Index and the non-clustered index. In this blog, we will discuss the difference between clustered and non clustered index in detail. Before proceeding the difference between clustered and nonclustered index in SQL Server, we’ll first brief each of them first.

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. 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 the “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: SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access

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

Read: How to Create Database in Microsoft SQL Server?

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 additional sorting required later.
  • It is usually fine putting the clustered index on the primary key of a table because the 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.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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. The major difference between clustered index and nonclustered index is that  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. 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:

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 –

Read: How to Increase the Speed of SQL Query Execution


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 the 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.

Read: What Is Average Salary Of Database Admin In Philippine?

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. 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: Most Popular SQL Server Performance Tuning Tips

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

There is a table whose values change 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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Final Words:

From the discussion, it is clear how clustered and nonclustered indexes are different. Let’s summarize here the difference between clustered and nonclustered indexes. 

  • There can be only one clustered index per table while non clustered 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.
  • The difference between clustered and non clustered index is that Clustered indexes are usually faster as they don’t demand the extra look space.

It is very important for you to know these differences because these days the difference between non clustered and nonclustered columnstore index is one of the most frequently asked questions of an interview related to any SQL related job profile. What is the  For more information on SQL indexing and its usage, join the SQL certification course online at JanBask Training and master the database concepts thoroughly.

Read: What Is Average Salary Of Database Admin In Philippine?

SQL Tutorial Overview


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

2 days 05 Aug 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-0 day 03 Aug 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

14 days 17 Aug 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 07 Aug 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 04 Aug 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

4 days 07 Aug 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

2 days 05 Aug 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

11 days 14 Aug 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

4 days 07 Aug 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

2 days 05 Aug 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

13 days 16 Aug 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

4 days 07 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews