What are Indexes in SQL?

Indexes in SQL are data structures that help speed up the retrieval of rows from a database table. Instead of scanning the entire table for a query, an index allows the database engine to quickly locate the data, similar to how an index in a book helps you find specific content without reading every page.

However, indexes come with a trade-off: they can slow down write operations (like INSERT, UPDATE, and DELETE) because the index also needs to be updated when data changes.

Types of Indexes in SQL

1. Clustered Index

A clustered index defines the physical order of data in a table.

Only one clustered index can exist per table because the rows themselves are physically stored in this order.

Typically, the primary key column is indexed using a clustered index.

Example of Clustered Index:

If you have a table Employees with EmployeeID as the primary key, creating a clustered index on EmployeeID will store the data physically in that order.

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50)
);

In the above case, a clustered index is automatically created on EmployeeID because it is the primary key.

Benefits of a Clustered Index:

Efficient for range queries (e.g., BETWEEN, >, <).

Faster retrieval when querying by the clustered index column.

1. Non-Clustered Index

A non-clustered index creates a separate structure that stores a pointer to the physical data in the table, rather than sorting the data itself.

You can have multiple non-clustered indexes on a table.

They are used to speed up queries that frequently search on non-primary key columns.

Example of Non-Clustered Index:

Let's say you often query the Employees table by LastName. Creating a non-clustered index on LastName will speed up those queries.

CREATE NONCLUSTERED INDEX idx_lastname
ON Employees(LastName);

Now, when you search for employees by their last name, the database engine uses the non-clustered index to locate rows faster.

Benefits of a Non-Clustered Index:

Improves performance on queries using non-primary key columns.

Multiple non-clustered indexes can be created on a table to optimize various queries.

Key Differences Between Clustered and Non-Clustered Indexes

Examples in Practice

Clustered Index: Imagine a library storing books in alphabetical order by title (like a clustered index on BookTitle).

Non-Clustered Index: Now imagine the same library having a card catalog that lets you find books by author (like a non-clustered index on Author), without changing how the books are physically stored.

Conclusion:

Use a clustered index when you need to retrieve data based on the order of a key (e.g., primary key, timestamps).

Use a non-clustered index when you frequently query other columns (e.g., LastName, Email) to speed up retrieval.