Indexes Introduction
What is an Index?
An Index in SQL Server is a database object used to improve the speed of data retrieval operations. Indexes help SQL Server find records faster without scanning the entire table.
An index works similar to an index in a book. Instead of checking every page to find a particular topic, we can use the index to quickly locate the required information.
Why are Indexes Used?
Indexes are mainly used to improve the performance of SELECT queries and make data retrieval faster.
- Indexes improve query performance.
- They help SQL Server find records quickly.
- They reduce the amount of data SQL Server needs to scan.
- Indexes are useful for columns frequently used in WHERE clauses.
- They can improve JOIN and ORDER BY operations.
- Indexes can also improve searching and sorting operations.
Simple Example
Suppose we have a Students table:
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
City VARCHAR(50),
Age INT
);
Suppose the table contains thousands of student records and we frequently search students based on the City column.
SELECT *
FROM Students
WHERE City = 'Pune';
Creating an index on the City column can help SQL Server find the required records more efficiently.
Creating an Index
A non-clustered index can be created using the CREATE INDEX statement.
CREATE INDEX IX_Students_City
ON Students(City);
Here:
- IX_Students_City is the name of the index.
- Students is the table name.
- City is the column on which the index is created.
Types of Indexes
SQL Server supports different types of indexes. The commonly used types are:
- Clustered Index
- Non-Clustered Index
In the upcoming topics, we will learn about both clustered and non-clustered indexes in detail.
Clustered Index
A Clustered Index determines the physical order of data in a table.
A table can have only one clustered index because the physical data can be stored in only one order.
CREATE CLUSTERED INDEX IX_Students_StudentId
ON Students(StudentId);
Non-Clustered Index
A Non-Clustered Index is a separate structure that stores indexed column values along with references to the actual rows in the table.
A table can have multiple non-clustered indexes.
CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City);
Index and SELECT Query
Consider the following query:
SELECT StudentId,
StudentName,
City,
Age
FROM Students
WHERE City = 'Pune';
If an appropriate index exists on the City column, SQL Server may use the index to locate matching records more efficiently.
Important Points
- Indexes are mainly used to improve data retrieval performance.
- Indexes are useful for frequently searched columns.
- A table can have only one clustered index.
- A table can have multiple non-clustered indexes.
- Indexes require additional storage space.
- Too many indexes can affect INSERT, UPDATE and DELETE performance.
When Should We Use Indexes?
Indexes should be considered when a table contains a large amount of data and specific columns are frequently used for searching, filtering, joining or sorting.
- Columns used frequently in WHERE conditions.
- Columns frequently used in JOIN conditions.
- Columns frequently used for sorting.
- Columns frequently used for searching.
Remember
Indexes can significantly improve SELECT query performance, but they also require storage and can increase the cost of INSERT, UPDATE and DELETE operations. Therefore, indexes should be created carefully.
Summary
Indexes are database objects used to improve the performance of data retrieval operations in SQL Server. They help SQL Server find records faster instead of scanning the entire table.
The two commonly used types of indexes are Clustered Index and Non-Clustered Index. A table can have one clustered index and can have multiple non-clustered indexes.
In the next topic, we will learn about Clustered Index in detail.