Understanding Clustered Indexes in SQL Server: A Complete Guide

Why indexes?

Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. Index on a table or a view, is very similar to an index that we find in a book.

If you don't have an index in a book, and I ask you to locate a specific chapter in that book, you will have to look at every page starting from the first page of the book.

On, the other hand, if you have the index, you lookup the page number of the chapter in the index, and then directly go to that page number to locate the chapter.

Obviously, the book index is helping to drastically reduce the time it takes to find the chapter.

In a similar way, Table and View indexes, can help the query to find data quickly.

In fact, the existence of the right indexes, can drastically improve the performance of the query. If there is no index to help the query, then the query engine, checks every row in the table from the beginning to the end. This is called as Table Scan. Table scan is bad for performance.

Index Example:

At the moment, the Employees table, does not have an index on SALARY column.

Consider, the following query

Select * from  Employee where Salary > 14000 and Salary < 18000

To find all the employees, who has salary greater than 14000 and less than 18000, the query engine has to check each and every row in the table, resulting in a table scan, which can adversely affect the performance, especially if the table is large. Since there is no index, to help the query, the query engine performs an entire table scan.

Now Let's Create the Index to help the query:Here, we are creating an index on Salary column in the employee table

CREATE Index IX_Employee_Salary 
ON  Employee (SALARY ASC)

Now, when the SQL server has to execute the same query, it has an index on the salary column to help this query. Salaries between the range of 5000 and 7000 are usually present at the bottom, since the salaries are arranged in an ascending order. SQL server picks up the row addresses from the index and directly fetch the records from the table, rather than scanning each row in the table. This is called as Index Seek.

An Index can also be created graphically using SQL Server Management Studio

1. In the Object Explorer, expand the Databases folder and then specific database you are working with.
2. Expand the Tables folder
3. Expand the Table on which you want to create the index
4. Right click on the Indexes folder and select New Index
5. In the New Index dialog box, type in a meaningful name
6. Select the Index Type and specify Unique or Non Unique Index
7. Click the Add
8. Select the columns that you want to add as index key
9. Click OK
10. Save the table

To view the Indexes:

In the object explorer, expand Indexes folder. Alternatively use sp_helptext system stored procedure. The following command query returns all the indexes on Employee table.

Execute sp_helptext  Employee

To delete or drop the index:

When dropping an index, specify the table name as well

Drop Index Employee.IX_Employee_Salary

Clustered Index:

A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.

Create Employees table using the script below.

CREATE TABLE [Employee]
(
 [Id] int Primary Key,
 [Name] nvarchar(50),
 [Salary] int,
 [Gender] nvarchar(10),
 [City] nvarchar(50)
)

Note that Id column is marked as primary key. Primary key, constraint create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint. 

To confirm this, execute sp_helpindex Employee, which will show a unique clustered index created on the Id column. 

Now execute the following insert queries. Note that, the values for Id column are not in a sequential order.

Code:

Insert into Employee Values(3,'Jaman',12200,'Male','New York')
Insert into Employee Values(1,'Rambo',12300,'Male','London')
Insert into Employee Values(4,'Azam',16200,'Male','Tokyo')
Insert into Employee Values(5,'Samser',13300,'Male','Toronto')
Insert into Employee Values(2,'Inza',14300,'Male','Sydney')

Execute the following SELECT query

 
Select * from Employee

Inspite, of inserting the rows in a random order, when we execute the select query we can see that all the rows in the table are arranged in an ascending order based on the Id column. This is because a clustered index determines the physical order of data in a table, and we have got a clustered index on the Id column.

Because of the fact that, a clustered index dictates the physical storage order of the data in a table, a table can contain only one clustered index. If you take the example of Employee table, the data is already arranged by the Id column, and if we try to create another clustered index on the Name column, the data needs to be rearranged based on the NAME column, which will affect the ordering of rows that's already done based on the ID column.

For this reason, SQL server doesn't allow us to create more than one clustered index per table. The following SQL script, raises an error stating 'Cannot create more than one clustered index on table 'Employee'. Drop the existing clustered index PK__Employee__3214EC0706CD04F7 before creating another.'

Code:

Create Clustered Index IX_Employee_Name
ON  Employee(Name)

A clustered index is analogous to a telephone directory, where the data is arranged by the last name. We just learnt that, a table can have only one clustered index. However, the index can contain multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

Let's now create a clustered index on 2 columns. To do this we first have to drop the existing clustered index on the Id column. 

Drop index Employee.PK__Employee__3214EC070A9D95DB

When you execute this query, you get an error message stating 'An explicit DROP INDEX is not allowed on index 'Employee.PK__Employee__3214EC070A9D95DB'. It is being used for PRIMARY KEY constraint enforcement.' We will talk about the role of unique index in the next session. To successfully delete the clustered index, right click on the index in the Object explorer window and select DELETE.

Now, execute the following CREATE INDEX query, to create a composite clustered Index on the City and Salary columns.

Create Clustered Index IX_Employee_Gender_Salary
ON  Employee(City DESC, Salary ASC)

Now, if you issue a select query against this table you should see the data physically arranged, FIRST by City in descending order and then by Salary in ascending order. The result is shown below.