An index is a data structure that is used to improve the performance of database queries. It is created on one or more columns in a table and allows the database to quickly locate the data that matches the query.

When working with large databases, one of the biggest challenges is finding information quickly.
If a table contains thousands or even millions of rows, searching through it one record at a time would be slow and inefficient.
To solve this problem, SQL provides a powerful tool called an index.

An index in SQL works very much like the index at the back of a textbook.
If you want to find a topic in a book, you don’t flip through every page—you go to the index, look up the keyword, and jump directly to the page number.
In the same way, an index helps the database locate the exact rows it needs without scanning the entire table.

An index is a special data structure that improves the speed of data retrieval operations on a table by creating fast lookup paths, similar to how a book index points you to the right page.

Why Do We Use Indexes?

Indexes help:

• Speed up SELECT queries
• Make searching, filtering, and sorting much faster
• Reduce the time needed for JOIN operations
• Improve the overall performance of a database

Without indexes, the database may need to perform a “full table scan,” which is slow and resource-heavy.

Simple Example

Suppose you have a table of employees, and you often search by EmployeeID.
Creating an index on EmployeeID allows SQL to jump straight to the matching records instead of checking each row.

CREATE INDEX idx_EmployeeID
ON Employees(EmployeeID);

Now, queries like:

SELECT * FROM Employees WHERE EmployeeID = 105;

will run much faster.

Important Note

Indexes improve read performance, but they may slow down:

• INSERT
• UPDATE
• DELETE

This is because the index itself must also be updated whenever data changes.
So indexes must be used wisely—too many can hurt performance.

Types of Indexes (Simple Overview)

1. Clustered Index
   • Sorts and stores the data rows in the table itself
   • A table can have only one clustered index
2. Non-Clustered Index
   • Stores a separate structure pointing to the data
   • A table can have multiple non-clustered indexes
3. Unique Index
   • Ensures no duplicate values exist
4. Composite Index
   • Index on more than one column