Indexes in SQL - Clustered Index and Non-Clustered Index

Indexs

Indexes in SQL are database objects that improve the speed of data retrieval operations on a table at the cost of additional storage space and some overhead during data modification operations (inserts, updates, deletes). An index is created on a table to allow faster access to rows in that table. They work similarly to an index in a book, which allows you to find information quickly without having to read every page.

Indexes in SQL are data structures that improve the speed of data retrieval operations on a database table at the cost of additional space and slower write operations. They are crucial for optimizing performance, especially in large databases.

Clustered and Non-Clustered Index with Sample Example

Let’s use a simple table called employees to demonstrate clustered and non-clustered indexes.

Clustered Index Example

A clustered index sorts the actual data rows in the table. By default, the primary key creates a clustered index.

Step 1: Create Table with a Primary Key (Clustered Index)

CREATE TABLE employees (
id INT PRIMARY KEY, -- This will automatically create a clustered index
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

Here, the id column is the primary key, and a clustered index is automatically created on it. The data in the employees table is stored in the order of the id values.

Query using the Clustered Index:

SELECT * FROM employees WHERE id = 5;

Since id is the clustered index, the data will be retrieved efficiently.

Non-Clustered Index Example

A non-clustered index creates a separate structure with a pointer to the actual data.

Step 2: Create a Non-Clustered Index on the name Column

CREATE NONCLUSTERED INDEX idx_employee_name ON employees(name);

This creates a non-clustered index on the name column. The index structure contains the name values and pointers to the rows where the data is stored.

Query using the Non-Clustered Index:

SELECT * FROM employees WHERE name = 'John';

The database engine will use the non-clustered index on the name column to quickly find the row where name = 'John' and then retrieve the corresponding data from the table.

Key Differences in this Example:

The clustered index on id means the data is physically stored in the order of id. When searching by id, the query is very fast.

The non-clustered index on name means that the data is stored separately, and when you query by name, the database uses the non-clustered index to find the row quickly but still retrieves the actual data from the table.

Conclusion:

Clustered Index: Orders the actual table data (e.g., id column).

Non-Clustered Index: Creates a separate index for faster lookups (e.g., name column).