Mar 22, 2019 
207.1k
13
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
- SQL Server Blogs -
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
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.
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: SQL Developer Resume: Examples, Samples, & Tips (2024)
SQL Server Training & Certification
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: What is SQL Subquery? Types of Subqueries in SQL
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 |
Read: What is SQL Formatter? Features of SQL Formatter Online
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
From the discussion, it is clear how clustered and nonclustered indexes are different. Let’s summarize here the difference between clustered and nonclustered indexes.
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: Step By Step SSAS Tutorial For Beginners
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Apr 29, 2020
7.1k
7.1k