Indexing in SQL

What are Indexes in SQL?
Indexes in SQL are special data structures designed to optimize the speed of data retrieval operations from database tables. Rather than scanning the entire table for the required information, indexes allow the database engine to quickly locate data, similar to an index in a book that helps you find specific topics without having to read every page.

However, while indexes enhance read performance, they can introduce overhead for write operations (INSERT, UPDATE, DELETE) because the indexes must also be updated whenever the data changes.

Types of Indexes in SQL

1. Clustered Index

• Definition: A clustered index determines the physical order of data in a table. There can only be one clustered index per table because the data rows themselves are stored in this order.
• Example: In a table Employees with EmployeeID as the primary key, creating a clustered index on EmployeeID means the data will be stored physically in the order of EmployeeID.

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

Benefits:

• Efficient for range queries (e.g., BETWEEN, >, <).
• Faster retrieval for queries filtering on the clustered index column.

2. Non-Clustered Index

• Definition: A non-clustered index creates a separate structure that maintains a pointer to the physical data in the table. You can create multiple non-clustered indexes on a single table.
• Example: If you frequently query the Employees table by LastName, you can create a non-clustered index on LastName to optimize those queries.

CREATE NONCLUSTERED INDEX idx_lastname
ON Employees(LastName);

Benefits:

• Improves performance for queries on non-primary key columns.
• Allows multiple non-clustered indexes for various query optimizations.

Key Differences Between Clustered and Non-Clustered Indexes

Storage:

• Clustered indexes determine the physical storage order of data.
• Non-clustered indexes maintain a separate structure with pointers to the actual data.

Number of Indexes:

• A table can have only one clustered index.
• A table can have multiple non-clustered indexes.

Examples in Practice

• Clustered Index: Think of a library that organizes books alphabetically by title. This is akin to a clustered index where the data (books) is sorted based on a key (title).
• Non-Clustered Index: Imagine a library with a card catalog that allows you to find books by the author. This functions like a non-clustered index, which enables faster searches without changing how the books are physically arranged.

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 enhance retrieval performance.