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
- 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.
- 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.
Source link
lol
