SQL Topics
Non-Clustered Index
title: Non-Clustered Index
In the previous lesson, you learned about Clustered Indexes, which physically organize table data according to the indexed column.
However, a table can have only one clustered index because data can be physically sorted in only one way.
What if users frequently search using:
Email
Phone Number
Department ID
Username
Product Codeinstead of the primary key?
To solve this problem, databases provide Non-Clustered Indexes.
A Non-Clustered Index stores a separate structure containing indexed values and pointers to the actual rows. Unlike a Clustered Index, it does not change the physical order of table data.
Non-Clustered Indexes are among the most commonly used performance optimization techniques in modern databases.
What is a Non-Clustered Index?
A Non-Clustered Index is a separate database structure that stores:
Indexed Value
+
Pointer To RowThe actual table data remains unchanged.
Think of it as:
Book Index
ā
Page Number
ā
Actual ContentThe index helps locate the row quickly.
Why is it Called Non-Clustered?
Unlike a Clustered Index:
Data Itself
Is Not SortedThe index exists separately.
Example:
Table:
EmployeeID
5
2
9
1A Non-Clustered Index on Email:
rahul@gmail.com ā Row 1
priya@gmail.com ā Row 2The data order remains unchanged.
Why Are Non-Clustered Indexes Important?
Suppose a table contains:
10 Million EmployeesUsers search by:
Email
Phone Number
UsernameWithout an index:
Full Table ScanWith a Non-Clustered Index:
Find Index Entry
ā
Follow Pointer
ā
Retrieve RowResult:
Much Faster SearchHow Non-Clustered Index Works
Process:
Create Index
ā
Store Indexed Values
ā
Store Row Pointers
ā
Locate Rows QuicklyVisual Representation
Table:
Row 1 ā Rahul
Row 2 ā Priya
Row 3 ā Amit
Row 4 ā NehaNon-Clustered Index:
Email
ā
Pointer
ā
Actual RowSearch becomes efficient without changing table order.
Creating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Email VARCHAR(100),
Salary DECIMAL(10,2)
);Insert records:
INSERT INTO Employees VALUES
(1,'Rahul','rahul@gmail.com',50000),
(2,'Priya','priya@gmail.com',60000),
(3,'Amit','amit@gmail.com',70000);Creating a Non-Clustered Index
SQL Server Example:
CREATE NONCLUSTERED INDEX
IDX_Email
ON Employees(Email);Now searches on:
Emailbecome faster.
Using the Index
Query:
SELECT *
FROM Employees
WHERE Email =
'rahul@gmail.com';Database uses:
IDX_Emailto locate the record quickly.
Internal Structure
A Non-Clustered Index typically contains:
Indexed Column
ā
Key Value
ā
Row LocatorExample:
amit@gmail.com
ā
Row 3Multiple Non-Clustered Indexes
Unlike Clustered Indexes:
Only One Clustered Indexbut:
Many Non-Clustered Indexescan exist.
Example:
CREATE NONCLUSTERED INDEX
IDX_Email
ON Employees(Email);
CREATE NONCLUSTERED INDEX
IDX_Name
ON Employees(EmployeeName);
CREATE NONCLUSTERED INDEX
IDX_Salary
ON Employees(Salary);Non-Clustered Index on Email
Example:
CREATE NONCLUSTERED INDEX
IDX_Email
ON Employees(Email);Useful for:
SELECT *
FROM Employees
WHERE Email = ?;Non-Clustered Index on Salary
Example:
CREATE NONCLUSTERED INDEX
IDX_Salary
ON Employees(Salary);Useful for:
SELECT *
FROM Employees
WHERE Salary > 50000;Composite Non-Clustered Index
Multiple columns can be indexed.
Example:
CREATE NONCLUSTERED INDEX
IDX_NameSalary
ON Employees
(
EmployeeName,
Salary
);Useful when queries frequently use both columns.
Clustered vs Non-Clustered Index
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Physical Data Sorting | Yes | No |
| Storage | Data Itself | Separate Structure |
| Number Allowed | One | Multiple |
| Range Queries | Excellent | Good |
| Lookup Performance | Very Fast | Fast |
How Searches Work
Clustered Index
Index
=
DataDirect access.
Non-Clustered Index
Index
ā
Pointer
ā
DataRequires an additional lookup step.
Real-World Example: Banking
Table:
CustomersClustered Index:
CustomerIDNon-Clustered Index:
Email
Phone Number
PAN NumberAllows fast searches using alternative fields.
Real-World Example: E-Commerce
Users search products by:
SKU
Product Name
CategoryNon-Clustered Indexes improve performance.
Real-World Example: Education System
Students searched by:
Roll Number
Email
Registration NumberNon-Clustered Indexes accelerate lookups.
Advantages of Non-Clustered Index
Faster Searches
Reduces full table scans.
Multiple Indexes Allowed
Supports many access patterns.
Flexible Optimization
Different columns can be indexed.
Better Query Performance
Improves SELECT operations.
Improves JOIN Operations
Useful for foreign key lookups.
Disadvantages of Non-Clustered Index
Additional Storage
Indexes consume disk space.
Slower INSERT Operations
Indexes require updates.
Slower UPDATE Operations
Indexed values must be maintained.
Slower DELETE Operations
Index entries must be removed.
Additional Lookup Required
Data retrieval may require an extra step.
When Should You Use Non-Clustered Indexes?
Good candidates:
Email
Phone Number
Username
CustomerID
DepartmentID
Product CodeColumns frequently used in:
WHERE
JOIN
ORDER BY
GROUP BYclauses.
When Should You Avoid Them?
Avoid indexing:
Small Tables
Frequently Updated Columns
Low Selectivity ColumnsExample:
Gender
Status
Boolean Fieldsoften provide little benefit.
Performance Considerations
Non-Clustered Indexes improve:
SELECT
JOIN
ORDER BY
GROUP BYbut may slow:
INSERT
UPDATE
DELETEbecause index maintenance is required.
Common Errors
Creating Too Many Indexes
More indexes do not always improve performance.
Indexing Low-Cardinality Columns
Poor optimization benefit.
Ignoring Storage Costs
Indexes consume disk space.
Duplicate Indexes
Waste resources and maintenance effort.
Best Practices
Index Frequently Searched Columns
Focus on real workloads.
Monitor Query Plans
Verify index usage.
Avoid Excessive Indexing
Only create useful indexes.
Use Composite Indexes Carefully
Column order matters.
Review Indexes Periodically
Remove unused indexes.
Common Interview Questions
What is a Non-Clustered Index?
A separate index structure that stores indexed values and pointers to table rows.
Does a Non-Clustered Index change table order?
No.
The table data remains unchanged.
How many Non-Clustered Indexes can a table have?
Multiple.
The exact limit depends on the database system.
What is the main advantage?
Fast searching without changing physical data order.
What is the difference between Clustered and Non-Clustered Indexes?
Clustered indexes organize data physically, while Non-Clustered indexes store pointers to data.
Summary
A Non-Clustered Index is a powerful database optimization feature that stores indexed values separately from table data. It improves search speed, supports multiple indexing strategies, and allows databases to efficiently handle a wide variety of queries.
In this lesson, you learned:
- What a Non-Clustered Index is
- How it works
- Internal structure
- Creating Non-Clustered Indexes
- Composite Indexes
- Clustered vs Non-Clustered Indexes
- Advantages and disadvantages
- Performance considerations
- Best practices
Mastering Non-Clustered Indexes is essential because they are one of the most widely used tools for improving SQL query performance in real-world database systems.
Next Step
Continue to the next lesson:
Unique Index ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Non-Clustered Index.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, indexing, non
Related SQL Topics