📘 SQL Server Index Tutorial
By: CodeInQueries
🔹 What is an Index in SQL Server?
An Index in SQL Server is a data structure that improves the speed of data retrieval
operations on a database table at the cost of additional space and slower write operations.
Think of it like an index at the back of a book—it helps you quickly find the page containing
the topic.
✅ Benefits of Indexes
1. Faster data retrieval – Especially for queries using WHERE, JOIN, ORDER BY, and GROUP
BY.
2. Helps enforce uniqueness – Through UNIQUE INDEX.
3. Improves performance of JOIN operations.
4. Supports fast sorting – Useful when querying large datasets.
5. Filtered Indexes help improve performance for specific queries.
❌ Limitations of Indexes
1. Slower INSERT, UPDATE, DELETE operations – Indexes need to be updated whenever
data changes.
2. Consumes extra disk space.
3. Too many indexes can confuse the SQL optimizer and degrade performance.
4. Index fragmentation over time needs regular maintenance (REBUILD or REORGANIZE).
🔍 Types of Indexes in SQL Server
- Clustered Index: Sorts data rows in the table itself. Only one per table.
- Non-Clustered Index: Separate from the table. Stores pointers to the data.
- Unique Index: Ensures all values in the indexed column(s) are unique.
- Composite Index: Index on two or more columns.
🧪 SQL Server Index Examples
Example 1: Create Table with Clustered Index (via Primary Key)
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
DepartmentID INT,
HireDate DATE
);
Example 2: Create Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Department
ON Employees(DepartmentID);
Example 3: Composite Index
CREATE NONCLUSTERED INDEX idx_Dept_Hire
ON Employees(DepartmentID, HireDate);
Example 4: Unique Index
CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNames
ON Employees(FirstName, LastName);
🧠 Top SQL Index Interview Questions & Answers
Q1. What is the difference between a Clustered and Non-Clustered Index?
Answer:
Clustered Index: Sorts data rows in the table itself. Only one per table. Faster for range
queries.
Non-Clustered Index: Separate structure that stores pointers. Multiple allowed. Best for
lookups.
Q2. How many Clustered Indexes can a table have?
Answer: Only one, because data can be sorted only one way physically.
Q3. What happens when a table has too many indexes?
Answer: Slows down INSERT/UPDATE/DELETE operations, increases storage usage, and
may confuse the query optimizer.
Q4. How to check if an index is being used in SQL Server?
Answer:
Use this query:
SELECT
OBJECT_NAME(IXOS.OBJECT_ID) AS TableName,
[Link] AS IndexName,
IXOS.*
FROM sys.dm_db_index_usage_stats AS IXOS
JOIN [Link] AS I ON I.OBJECT_ID = IXOS.OBJECT_ID AND I.index_id = IXOS.index_id
WHERE OBJECTPROPERTY(IXOS.OBJECT_ID,'IsUserTable') = 1;
Q5. Can a table have both clustered and non-clustered indexes?
Answer: Yes, one clustered and multiple non-clustered indexes are allowed.
📌 Summary
- Use Clustered Index on primary/identity columns.
- Use Non-Clustered Index on frequently filtered or joined columns.
- Don’t over-index; it may harm performance.
- Monitor index usage using DMVs.
- Use Filtered and Covering Indexes for optimization.
- Regularly maintain indexes (REBUILD, REORGANIZE).