SQL Reference - CLUSTERED

CLUSTERED

CLUSTERED is a type of SQL index that determines the physical order of data in a table. It sorts and stores the data rows in the table based on their key values. Due to this ordering, there can only be one CLUSTERED index per table, though multiple non-clustered indexes can exist concurrently. This type of index significantly improves data retrieval speed when searching by the key column.

Example

CREATE TABLE Orders
(
    OrderID int PRIMARY KEY CLUSTERED,
    OrderNumber int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    OrderDate date
);

Output

Commands executed successfully.

Explanation

The given SQL statement creates a new table named Orders with OrderID, OrderNumber, CustomerName, and OrderDate fields. The OrderID field is defined as the primary key and it is clustered. In SQL Server, a clustered index determines the physical order of data in a table, thus the table data rows are sorted and stored in order according to the clustered index. Here, the OrderID field is the clustered index of the Orders table.

Example

CREATE CLUSTERED INDEX idx_name
ON table_name (column_name);

Output

Index created.

Explanation

In the provided example, a CLUSTERED index named idx_name is created on the column column_name of the table table_name. The CLUSTERED index physically reorders the rows in the table to match the index.