How to Create Clustered Index in SQL Server

by

If you want to know how to create clustered in SQL Server, this tutorial is for you.

As the index improves the query performance, the clustered index plays a very important role in saving disk space and improving the speed of operation on the table.

This tutorial is about clustered index, where I will explain what it is and how it is helpful with syntax.

Then I will show how to remove the existing clustered index and create your clutered index on table, additionally, I will explain when to use clustered index.

What is a Clustered Index in SQL Server

A clustered index in SQL Server is a type of index that specifies the physical order of data in a table where table data (records) are stored.

  • Also, note that the table’s data can be stored only in one order, so there can be only one clustered index on a table.

Whenever a new cluster index is created on the table, the table rows are stored on disk in the order of the index key.

The syntax to create a clustered index is given below.

CREATE CLUSTERED INDEX index_name
on table_name (column1, column2, ....)

Where,

  • CREATE CLUSTERED INDEX: This command creates a non-clustered index in SQL Server. The CLUSTERED keyword is used when creating a clustered index on a table.
  • index_name: Name the index you want to create on a table.
  • on table_name: specify the table name on which you want to create a non-clustered index.
  • (column1, column2, ..): Specify a single or multiple-column name of the table that will be used for indexing.

Create Clustered Index in SQL Server

Let’s see an example of creating an SQL server clustered index on a table. First, use the query below to create a table named Store with columns stored_id, store_name and store_location.

CREATE TABLE Store (
	store_id INT PRIMARY KEY,
	store_name NVARCHAR(100),
	store_location NVARCHAR(255)
);
Create Clustered Index in SQL Server Store Table

After executing the above query, a table named Store is created; here, the purpose of creating a table is to show you that clustered indexes are created by default on the table’s primary key column.

To view the indexes on the Store table, use the command below.

EXEC sp_helpindex 'Store';
Viewing Created Clustered Index in SQL Server

From the output, the index name is PK__Store__A2F2A30CCC01D340; the index_description says it is a clustered unique index, and index_keys are based on the store_id column.

The index is created by default based on the table’s primary key columns. This is the default one, but you can use the syntax above to create your own clustered index in SQL Server.

For example, let’s take an example of the Employee’s table, which is shown below.

Create clustered index in SQL Server Employees Table

View the indexes of the above table using the command below.

EXEC sp_helpindex 'Employees';
Viewing Created Clustered Index in SQL Server of Employees Table

First, remove the existing clustered index to add a new one with a different order of the data and again view the index on the Employees table. For that, use the command below.


ALTER TABLE Employees
DROP CONSTRAINT PK__Employee__7AD04FF172A8D366;

After executing the query, when the constraint primary key is deleted, clustered_index is automatically deleted.

Let’s create a new clustered index using the query below.

CREATE CLUSTERED INDEX idx_tblEmployees
ON Employees(FirstName DESC)

Using the above command, create a clustered index named ‘idx_tblEmloyees’ on the Employees table, and the index key is FirstName column, and setting the order of the column value to descending order, which means an employee with the highest value will be the first employee.

View the created index using the query below.

EXEC sp_helpindex 'Employees';
Create clustered index in SQL Server on Employees Table

Now, let’s view the employee’s table using the query below.

SELECT * FROM Employees;
View Employees Table For Checking the Order the Index Column

From the output, the customer with the highest ID is the first record, for example, an employee with an ID equal to 100000. This is due to the clustered index because the default order of the index is descending.

  • Remember, a clustered index affects the data stored in a table, physically changing the order of column values.

This is how to create clustered index in SQL Server.

Using the Clustered Index

If you have columns like age, etc., use the clustered index because data is sorted, so it will be easier to retrieve the data, unlike non-clustered, which takes time because it has to maintain separate data.

If you have a disk issue, consider using the clustered index, as it doesn’t use additional space compared to the non-clustered index, which is stored at different locations with additional space.

Now, to remember, each table should have a clustered index on a column containing unique values; often, the primary key column is used for a clustered index.

Conclusion

In this SQL Server tutorial, you have learned how to create clusterd index in SQL Server using the CREATE CLUSTERED INDEX command, also you have learend how to remove the alredy existing clusterd index from table and create a new one.

Finally, you learned when to use a clustered index.

You may like to read: