Understanding SQL Server Clustered Index with an example

A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column. Otherwise we can say in clustered index the rows are stored physically on the disk in the same order as the index

Clustered index example:-

Let us create a table called Users with userid and username columns.

 Create Table Users
(
UserID int not null,
UserName varchar(100)
)

Now insert rows into Users table,

INSERT INTO Users(UserID,UserName)
VALUES(1,'Ganesh')

INSERT INTO Users(UserID,UserName)
VALUES(2,'Muruga')

INSERT INTO Users(UserID,UserName)
VALUES(3,'Siva')

INSERT INTO Users(UserID,UserName)
VALUES(4,'Karthick')

INSERT INTO Users(UserID,UserName)
VALUES(5,'SenthilAndavar')

The users table does not have a primary key,therefore the database stores its rows in an unordered structure called a heap. So when you query from users table ,the query optimizer needs to scan the whole table to locate the correct record. For example,if the user wants to fetch 4th record from the table.

                         select userid,username from users where userid=4

The above query retrieve records faster because it has only 5 rows.Suppose if you have 1 lakh records then it takes lot of time to display records. so in order to speed up the retrieval process of records, we are going to create clustered index. A clustered index stores data in sorted structure based on its key values.Each table has only one clustered index.

Sql Server Clustered index with example, Normally when you create a table with primary key, sql server automatically creates corresponding clustered index based on columns included in the primary key.

 CREATE TABLE Users
( UserID int,
  UserName varchar(100),
 PRIMARY KEY(UserID)
);

Now if you execute the following query, you will see the results so fast without scanning the whole table.

                    
SELECT UserID,UserName FROM Users
WHERE UserID = 4

See the query execution plan now,