SQL Server Clustered Index

In previous post, we have learnt about Index and its types. In this post, we will learn about clustered index.

The syntax to create clustered index in SQL Server is as follows.

CREATE CLUSTERED INDEX [IndexName]
ON TableName (Column1, Column2, ...);

Let's break down this syntax:

• CREATE CLUSTERED INDEX: This statement is used to create a clustered index.
• IndexName: Replace this with a meaningful name for your index. It's optional, and if you omit it, SQL Server will generate a name.
• ON TableName: Specify the name of the table on which you want to create the clustered index.
• (Column1, Column2, ...): List the columns on which the index will be created. The order of columns is essential since it determines the order of the data in the clustered index.

Example: Suppose you have a table called Employee with columns ID and LastName, and you want to create a clustered index on the ID column:

CREATE CLUSTERED INDEX IX_Employee_ID
ON Employee (ID);

This will create a clustered index named IX_Employee_ID on the Employee table, using the ID column. The data in the table will be physically ordered based on the values in the ID column.

Points to remember:

• When clustered index is created, the existing data in the table is reordered to match the order of the index.
• If the table already has a clustered index, it MUST be dropped before creating a new one because only one clustered index is allowed in a table.
• The indexed column must be chosen judicially as per the need of query.

Example of Implicit Clustered Index

Create the following table.

CREATE TABLE EmployeesPkIndex(
     id int primary key,
     first_name varchar(10),
     last_name varchar(10)
)

We find that Index is created automatically on Id column.

Insert the following rows into the table.

INSERT EmployeesPkIndex Values(2, 'Ram', 'Kumar')
INSERT EmployeesPkIndex Values(1, 'Gaurav', 'Singh')
INSERT EmployeesPkIndex Values(5, 'Rima', 'Jain')

We find that row is automatically sorted on Id column due to clustered index on id.

SELECT * FROM EmployeesPkIndex

In the next post we will learn about Nonclustered Index in SQL Server.