SQL Server Indexes: Clustered Indexes

An Index in SQL Server is a data structure associated with tables and views that helps in faster retrieval of rows.

Data in a table is stored in rows in an unordered structure called Heap. If you have to fetch data from a table, the query optimizer has to scan the entire table to retrieve the required row(s). If a table has a large number of rows, then SQL Server will take a long time to retrieve the required rows. So, to speed up data retrieval, SQL Server has a special data structure called indexes.

An index is mostly created on one or more columns which are commonly used in the SELECT clause or WHERE clause.

There are two types of indexes in SQL Server:

Clustered Indexes

The clustered index defines the order in which the table data will be sorted and stored. As mentioned before, a table without indexes will be stored in an unordered structure. When you define a clustered index on a column, it will sort data based on that column values and store it. Thus, it helps in faster retrieval of the data.

There can be only one clustered index on a table because the data rows can be stored in only one order.

When you create a Primary Key constraint on a table, a unique clustered index is automatically created on the table.

As an example, create the following EmployeeDetails table that does not have a primary key.

Example: Create Table

CREATE TABLE dbo.EmployeeDetails(
	EmployeeID int NOT NULL,
	PassportNumber varchar(50) NULL,
	ExpiryDate date NULL
)

Insert the following rows in the table.

Example: Insert Data

Insert into EmployeeDetails values(3,'A5423215',null);
Insert into EmployeeDetails values(5,'A5423215',null);
Insert into EmployeeDetails values(2,'A5423215',null);
Insert into EmployeeDetails values(8,'A5423215',null);
Insert into EmployeeDetails values(1,'A5423215',null);
Insert into EmployeeDetails values(4,'A5423215',null);
Insert into EmployeeDetails values(6,'A5423215',null);
Insert into EmployeeDetails values(7,'A5423215',null);

The data in the EmployeeDetails table are not stored in any order. Execute the Select * from EmployeeDetails; and you will see the following result.

As you can see, the data is stored in the order in which they are inserted. Now, if you use the WHERE clause to filter out data, the query optimizer will scan the entire table rows to arrive at the required data. This is because the data is not in any order.

Create a clustered index on the EmployeeID column to sort and store the the data by EmployeeID. The data in the table will be sorted based on EmployeeID and executing the above query will return rows faster. This can be verified using SQL Server's execution plan.

Create Clustered Index Using T-SQL

The following is the syntax to create the clustered index on a table.

Syntax

CREATE CLUSTERED INDEX <index_name>
ON [schema.]<table_name>(column_name [asc|desc]);

The following creates a clustered index on the EmployeeID column of the EmployeeDetails table.

Example: Create Clustered Index

CREATE CLUSTERED INDEX CIX_EmpDetails_EmpId
ON dbo.EmployeeDetails(EmployeeID)

The above statement will crean an index in the indexes folder, as shown below.

Now, Select * from EmployeeDetails; will return the following result.

In a clustered index, the data is organized using a special structure called B-tree or a balanced tree structure. In B-tree, the top node is called the root node and the bottom nodes are called the leaf nodes. All index levels between the root and the leaf nodes are called intermediate levels. The leaf nodes contain the data pages. The root and intermediate levels contain index pages holding index rows and each index row contains a pointer either pointing to a data row in leaf node or to another intermediate level page. The pages in each level of the index are linked in a doubly linked list.

A clustered index can be created on two columns with different sorting order, as shown below.

Example: Create Clustered Index

CREATE CLUSTERED INDEX CIX_EmpDetails_EmpId
ON dbo.EmployeeDetails(EmployeeID ASC, PassportNumber DESC)

Create a Clustered index using SSMS

Step 1: Open SSMS and connect to the database. In the Object Explorer, expand the table where you want to create a clustered index.

Step 2: Right-click on the Indexes folder. Point to New Index and, select Clustered index.., as shown below.

Step 3: In the New Index dialog box, on the General page, enter a name of an index under the Index Name and click on the Add button under the Index Key Columns, as shown below.