Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

Home Interviews What is SQL Clustered and Non-Clustered key? – MindStick
Ask Question

1 Answers

ICSM Computer
ICSM Computer 22 Mar 2026
Report Abuse

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.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md