Clustered Index in SQL Server

In this article I would like to talk about Indexes in SQL Server. Indexes are used to improve the performance of the queries. Database Index is similar to the one at the back of any book. You have one big book with 1000 pages and want to find information about Transactions then either you can read page by page till you find the information or just open the Index page and look for the information and use that information to find the full length content.

Index is a separate object by itself. SQL Server automatically updates the indexes based on any data modifications to the table data.

However, you need to maintain the indexes for better performance.

There are mainly two types of indexes:
1) Clustered Index.
2) Non-Clustered Index.

The only differentiating factor between them is interims of storage.

Clustered Index:
Clustered index is a special type of index which forces the SQL Server to store the data based on the Cluster Key. Cluster key is nothing but the columns in the index. When you create the Clustered Index on a table, SQL Server physically orders the database on the Cluster Key. Table can be physically sorted in only one way. So that’s the reason you can have only one clustered index per table.

When you create a primary key on any table, by default SQL Server creates the clustered index.

Create a Clustered Index on Primary Key Column:

1) First let’s create a student table
CREATE TABLE STUDENT (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))

2) Lets define Primary key on STUDENT_ID column
ALTER TABLE STUDENT
ADD CONSTRAINT PK_STUDENT PRIMARY KEY (STUDENT_ID)

Create a Clustered Index on Non Primary Key Column:

1) First let’s create a student table
CREATE TABLE STUDENT_EXAM_RESULTS (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
RANK INT)

2) Let’s define Non clustered primary key on STUDENT_ID column
ALTER TABLE STUDENT_EXAM_RESULTS
ADD CONSTRAINT PK_ STUDENT_EXAM_RESULTS PRIMARY KEY NONCLUSTERED (STUDENT_ID)

3) Lets create a Clustered Index on RANK column.
CREATE CLUSTERED INDEX cidx_rank ON STUDENT_EXAM_RESULTS (RANK)


Clustered Index Design Guidelines:
You can have only one clustered index per table so you have to be very careful interims of selecting the right column and right index type.
Guidelines are:
1) Querying the table based on range of values
2) No updates to the cluster key columns.