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) |
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'); |
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.
