Indexes in SQL | Clustered 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 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).

Comments

[italanchan]

in real case, id column is not possible to use to search and the clustered index become useless index. Many companies are use composite keys as primary key, is it best also use as clustered index?