SQL Server 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.

Example 1

Creating a Clustered Index on a PRIMARY KEY

CREATE TABLE Orders (
OrderID INT PRIMARY KEY CLUSTERED,
CustomerID INT,
OrderDate DATETIME
);

In this example, we're creating a table called "Orders" with a primary key column called "OrderID". The CLUSTERED keyword indicates that this primary key will also be the clustered index for the table.

Example 2

Creating a Clustered Index on a Non-Primary Key

CREATE TABLE Customers (
CustomerID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(20)
);

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.

Example 3

Adding a Clustered Index to an Existing Table

CREATE TABLE Products (
ProductID INT NOT NULL,
ProductName VARCHAR(100),
CategoryID INT,
Price DECIMAL(10,2)
);

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 CLUSTERED INDEX. This can help improve performance for queries that join or filter on this table using the primary key.