SQL INDEXES

2 min readMay 17, 2024

Introduction

Hello data enthusiast! In today’s article we will have a look at SQL indexes. Indexes are database objects that improve the performance of data retrieval operations by allowing users to quickly find and locate specific data in a table. There are different types of indexes in SQL, with the most common being non-clustered and clustered indexes.

Non-Clustered Indexes

A good example of a non-clustered index is a textbook index. Non-clustered indexes do not alter the physical order of data in a table, instead they create a separate data structure to store the index data. In relational database management systems, a table can have multiple non-clustered indexes.

Syntax:

CREATE INDEX index_name
ON table_name(column);

index_name: The name of the index you are creating. It must be unique within the database.
table_name: The name of the table on which you are creating the index.
column: The column or columns on which you want to create an index.

Example:

The following code creates an index named ix_salary on the salary column in the employee table.

CREATE INDEX ix_salary
ON employee(salary);

Clustered indexes

Clustered indexes determine the physical order of data in a table. In relational databases, a table can only have one clustered index. A clustered index can include multiple columns, which is referred to as a composite index.

Get Diana Injelwa’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Note: A primary key constraint will automatically create a clustered index on that column.

Syntax:

CREATE CLUSTERED INDEX ix_customer_id
ON customer(customer_id);

Differences between clustered and non-clustered indexes

1. Number of indexes:

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

2. Performance:

Clustered indexes are generally faster because they determine the physical order of data and do not require additional lookup steps.
Non-clustered indexes refer back to the table if the selected column is not present in the index, which can introduce a slight performance overhead.

3. Storage:

Clustered indexes do not require extra storage space as they simply determine the order of rows in a table.
Non-clustered indexes are stored separately from the table, therefore requiring additional storage space.