Clustered indexes are a type of index that determine the physical order of the data in a table.
A table can have only one clustered index, because the data rows themselves can be stored in only one order.
The column or columns included in the clustered index are known as the key columns.
When a new row is inserted, the key columns are used to determine the appropriate location in the table for the new row.
Clustered indexes are useful for optimizing queries that retrieve a range of data based on the key column.
They also improve the performance of queries that join tables on the key column or columns.
Creating a clustered index also creates a unique constraint on the key column or columns.
To create a clustered index, you use the
CREATE CLUSTERED INDEX statement, and specify the column or columns that will be included in the index.
Creating a Clustered Index on a
CREATE TABLE Orders (
OrderID INT PRIMARY KEY CLUSTERED,
In this example, we're creating a table called "Orders" with a primary key column called "OrderID".
CLUSTERED keyword indicates that this primary key will also be the clustered index for the table.
Creating a Clustered Index on a Non-Primary Key
CREATE TABLE Customers (
CREATE CLUSTERED INDEX idx_customers_name
ON Customers (LastName, FirstName);
In this example, we're creating a table called "Customers" with a primary key column called "CustomerID". We're also creating a clustered index on the "LastName" and "FirstName" columns, which are not part of the primary key. This can help improve performance for queries that search or sort by these columns.
Adding a Clustered Index to an Existing Table
CREATE TABLE Products (
ProductID INT NOT NULL,
ALTER TABLE Products
ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID);
In this example, we're adding a clustered index to an existing table called "Products".
We're using the
ALTER TABLE statement to add a primary key constraint on the "ProductID" column, and specifying the
CLUSTERED keyword to create the
This can help improve performance for queries that join or filter on this table using the primary key.