SQL Server – Clustered and Non-Clustered

We have often searched SQL Server – Clustered and Non-Clustered while learning for SQL. Below are the basics for the same. I hope they will help you in understanding the concepts and eventually help you to create and write better queries and crack interviews.

SQL Server -Clustered and Non-Clustered

Let us create a sample DB for understanding in detail about SQL Server – Clustered and Non-Clustered

CREATE DATABASE Organizationdb
          
CREATE TABLE tbl_employee
(
    employeeid INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    post VARCHAR(50) NOT NULL,
    team VARCHAR(50) NOT NULL,
    age INT NOT NULL,
 )
 

Clustered Index

There can be only one clustered index. Clustered Index is like default sortation for a table. It makes the retrieval faster and helps you to make foreign key relationships with other tables as well. The primary keys or clustered indexes are mostly of int types, clustered Index is also known as the primary key.

Clustered indexes are physically stored and acts like a dictionary for the data row. The data is stored in order and It points the pointer to the data block where data is stored.

Let us run this query to identify the type of index created in the above table.

USE Organizationdb
          
EXECUTE sp_helpindex tbl_employee

While inserting the data in the table, even if you insert it in a random order, it will retrieve it always in order.

There is one more point that is mostly asked in interviews as well, when a clustered index is created on multiple columns it is called composite index/key. You can have only 1 clustered index in a table.

Non-Clustered Index

This is like the index or glossary you find in your book. It takes up a little extra space just like an extra page for them in books. You can have multiple non-clustered indexes in a table, as it is stored separately. It requires more memory and is comparatively a little slower than the clustered index.

You can create a non clustered index in the following way:

CREATE NONCLUSTERED INDEX [IX_employee_age] ON [dbo].[tbl_employee]
(
	[Age] ASC
)

Once created it will take up extra space and less read-write operations whenever this column is used.

You can’t drop any index non-key column unless you drop the index first. These non-key columns can’t be changed except changing it from NOT NULL to NULL or increasing the length of varchar, nvarchar, or varbinary columns.

Conclusion for SQL Server -Clustered and Non-Clustered

For SQL Server – Clustered and Non-Clustered you have to keep the following things in mind.

1. You can have only one clustered index which is the primary key and multiple non clustered indexes.
2. The logical order of the non clustered index does not match the physical order of the table created by the clustered index.
3. The clustered index takes up no space, where as non-clustered index takes up spaces to store data.
4. You have to carefully design your table, since having indexes may improve retrieval but it will directly impact your write operations as well.
5. Clustered Indexes are faster than non-clustered indexes
6. You can create a clustered index on multiple columns, it is called composite index/key.

FAQs

Make sure to follow our other posts as well below:

• Web Vitals – What are FCP, LCP, FID, TBT, TTFB?
• How to start with Distributed System Design: A Practical Guide for Scale and Reliability
• Rate Limiting – Token and Leaky Bucket Implementation and Explanation
• Software System Monitoring Tools
• Parking Lot – System Design HLD