interview

home / developersection / interviews / what is sql clustered and non-clustered key?

Ask Question
Ravi Vishwakarma

IT-Hardware & Networking

Ravi Vishwakarma is a dedicated Software Developer with a passion for crafting efficient and innovative solutions. With a keen eye for detail and years of experience, he excels in developing robust software systems that meet client needs. His expertise spans across multiple programming languages and technologies, making him a valuable asset in any software development project.

Message
Can you answer this question?
Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

22-Mar-2026

In SQL Server, Clustered and Non-Clustered keys are related to indexes. Indexes are used to improve query performance and speed up data searching in tables.

These are used in Microsoft SQL Server and managed using SQL Server Management Studio.

Note: Correct technical term is Clustered Index and Non-Clustered Index, but many people call them Clustered key / Non-clustered key.

1. What is Clustered Key (Clustered Index)?

A Clustered Index defines the physical order of data in the table.

This means:

  • Data in table is stored in sorted order
  • Only ONE clustered index allowed per table

Example

CREATE TABLE Students
(
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
)

Here, Primary Key creates Clustered Index by default.

Data will be stored like:

1 Rahul
2 Amit
3 Neha
4 Riya

Sorted by Id.

Key Points

  • Only one per table
  • Data stored in sorted order
  • Faster for range queries
  • Primary Key → Clustered by default

2. What is Non-Clustered Key (Non-Clustered Index)?

  • A Non-Clustered Index does NOT change physical order of table.
  • It creates a separate structure that points to actual data.

Think like:

Book index page → points to page number

Table data stays same, index stores pointers.

Example

CREATE NONCLUSTERED INDEX IX_Name
ON Students(Name)
  • Now searching by Name will be faster.
  • Data order in table does not change.

Key Points

  • Multiple allowed
  • Data not physically sorted
  • Stored separately
  • Faster search on specific column

3. Difference Between Clustered and Non-Clustered

Feature Clustered Non-Clustered
Physical order Yes No
Number per table 1 Many
Default on PK Yes No
Storage With table Separate
Speed Fast for range Fast for search
Pointer needed No Yes

4. Real Example

Table: Employees

Id  Name   Salary
3   Amit   20000
1   Rahul  30000
2   Neha   25000

Clustered on Id

Stored as:

1 Rahul
2 Neha
3 Amit

Non-Clustered on Name

Index:

Amit → row 3
Neha → row 2
Rahul → row 1

5. When to Use Clustered Index

Use when:

  • Primary key column
  • Frequently sorted column
  • Range queries used
  • Unique values

Example:

  • Id
  • Date
  • OrderNumber

6. When to Use Non-Clustered Index

Use when:

  • Search by Name
  • Search by Email
  • Search by Status
  • Filter queries

Example:

WHERE Name = 'Rahul'
WHERE Email = 'test@gmail.com'
WHERE Status = 1

7. Clustered vs Heap

  • Table without clustered index = Heap
  • Heap is slower for search.

8. Conclusion

  • Clustered Index → sorts actual data
  • Non-Clustered Index → creates pointer index

Both are important for performance tuning in SQL.

MindStick Training
MindStick Training

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy