Cluster Index in sql server

A clustered index defines the order in which data is physically stored in a table. A table can have only one clustered index because data rows can be only sorted in one order. When we are creating the primary key column of the table by default a cluster index is created. When a clustered index is created, SQL Server physically reorders the data in the table based on the primary key and values used in the indexed column(s).

A clustered indexes are made up of a set of pages called as index nodes that are organized in a B-tree structure. The top node of the B-tree is called as root node. The bottom nodes in the index are called as leaf nodes. Any index levels between the root and the leaf nodes are called as intermediate levels. The leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index.

Syntax of creating cluster index

CREATE CLUSTERED INDEX <index_name> ON <table_name> (<column_name> ASC/DESC)

 Let’s see how its work.

Suppose we have a table and we have stored data primary key data as below

Empid	Ename
1	Amit
6	Fatima
3	Chandan
2	Bagesh
5	Eshlok
8	Hitesh
7	Ganesh
15	Om Prakash
13	Mukesh
12	Lokesh
9	Ibrahim
10	Jagu
11	Kamlesh
14	Nagesh
4	Durgesh

Here we will see how the cluster index is stored. We know that cluster index store the data in a sorted list. It may be ascending or descending. It is depends what order we have given during the creation of cluster index. If we will not provide any order it will take ascending order by default. 

CREATE CLUSTERED INDEX IX_Emp_EmpID ON EMP (Empid ASC)

Index data would arrange in  binary tree (b tree) as below.

CREATE CLUSTERED INDEX IX_Emp_EmpID ON EMP (Empid DESC)

Index data would arrange in binary tree (b tree) as below.

Creating table, Index and inserting some records

--creating table CREATE table EMP (EMPID INT NOT NULL, EMPNAME VARCHAR(50) NOT NULL)  --creating index CREATE CLUSTERED INDEX IX_EMP_EMPID ON EMP (EMPID ASC)  --Inserting some records into this table insert into emp(Empid,empname) values (1        ,'Amit'); insert into emp(Empid,empname) values (6        ,'Fatima'); insert into emp(Empid,empname) values (3        ,'Chandan'); insert into emp(Empid,empname) values (2        ,'Bagesh'); insert into emp(Empid,empname) values (5        ,'Eshlok'); insert into emp(Empid,empname) values (8        ,'Hitesh'); insert into emp(Empid,empname) values (7        ,'Ganesh'); insert into emp(Empid,empname) values (15     ,'Om Prakash'); insert into emp(Empid,empname) values (13     ,'Mukesh'); insert into emp(Empid,empname) values (12     ,'Lokesh'); insert into emp(Empid,empname) values (9        ,'Ibrahim'); insert into emp(Empid,empname) values (10     ,'Jagu'); insert into emp(Empid,empname) values (11     ,'Kamlesh'); insert into emp(Empid,empname) values (14     ,'Nagesh'); insert into emp(Empid,empname) values (4        ,'Durgesh');

 Table and index created successfully. See the record in this table

  

We cannot create multiple clustered index on the table. Above we have created a cluster index lets create another cluster index on this table.

CREATE CLUSTERED INDEX IX_EMP_empname ON EMP (empname ASC)

Getting an error

To view the index we use below command

EXECUTE sp_helpindex emp;

We can also see in object explorer

To drop the index we will use the below command

drop index  IX_EMP_EMPID ON EMP

Index dropped successfully.

Index dropped

When we are creating a primary key on the table by default a clustered index is created.