Clustered Index: Definition, How It Works, and Usage

A clustered index in a database is a type of index that sorts and stores the actual data rows in the table based on the index key. It determines the physical order of data, making data retrieval faster for queries using that key.

How It Works

A clustered index organizes the data in a table so that the rows are stored in order based on the indexed column(s). Imagine a phone book where the pages are arranged alphabetically by last name; this is similar to how a clustered index arranges data physically on disk.

Because the data is stored in this sorted order, searching for a value using the clustered index is very fast, as the database can quickly jump to the right location. However, since the data itself is sorted, a table can have only one clustered index.

Example

This example shows how to create a clustered index on a table's column in SQL Server.

sql

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY CLUSTERED,
  Name VARCHAR(100),
  Department VARCHAR(50)
);

-- The clustered index is created on EmployeeID, so data is stored sorted by EmployeeID.

Output

Table 'Employees' created with clustered index on EmployeeID.

When to Use

Use a clustered index when you want fast retrieval of data based on a column that is frequently searched or sorted, such as an ID or date. It is ideal for columns with unique values and when range queries are common.

For example, in an employee database, a clustered index on EmployeeID helps quickly find employee records. However, since only one clustered index is allowed per table, choose the column that benefits most from sorted storage.

Key Points

• A clustered index sorts and stores the actual table data rows.
• Only one clustered index can exist per table because data can be sorted in only one order.
• It improves performance for queries that search or sort by the indexed column.
• Best used on columns with unique or mostly unique values.

Key Takeaways

A clustered index determines the physical order of data in a table based on the indexed column.

Only one clustered index is allowed per table because data can be sorted in only one way.

It speeds up queries that filter or sort by the clustered index key.

Choose columns with unique or mostly unique values for clustered indexes.

Clustered indexes are ideal for range queries and frequent lookups on the indexed column.