SQL Server indexes are used by queries to find data values quickly in tables and views. Basically it speed up the queries performance.
You can also relate a SQL Server Indexes with any text book indexes which helps to quickly locate information within the book, in similar way a SQL Server indexes is an ordered list of values and for each value there are pointers to the data pages where these values are located.
SQL Server provides following types of indexes:
- Clustered
- Non-clustered
- Unique
- Filtered
- Columnstore
- Hash
- Memory-Optimized Non-clustered
Here we are not going to cover all the indexes but only see the difference between clustered index and non-clustered.
Lets see the difference between clustered and non-clustered index in SQL Server.
CLUSTERED INDEX
A clustered index determine the order in which data rows are physically stored in a table. Table data rows can be sorted in only way, that is the reason, a table can have only one clustered index. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index.
In SQL Server, the primary key constraint automatically creates a clustered index on table columns, if no clustered index already exists on that table, and in case if you try to enforce a primary key constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a non-clustered index.
As table can have only one clustered index. However a clustered index can also be created on multiple columns which is called as composite clustered index.
Lets demonstrate the behaviour of clustered index with the help of following example.
Following script creates a table that have a primary key on column EmpId.
As we have created a table having primary key on column EmpId that would automatically creates a clustered index on the EmpId column.
Lets check if a table has created a clustered index, there are two way to check either in SSMS object explorer or using a SQL Server system stored procedure.
Lets check it in object explorer, expand the databases folder > expand the database> next, click on table Emp and expand the indexes folder.
You can see, a clustered index is created automatically that we have not created. It is created automatically because we have created a primary key on table.
Lets check the index using a SQL Server system stored procedure as shown below.
EXECUTE sp_helpindex Emp
You can see, a clustered index is created on column EmpId.
This clustered index stores the record in the Emp table in the ascending order of the EmpId column.
Which means if you insert a EmpId = 2 first in table then EmpId = 1, and after that you fetch the records from table, it always return a Empid =1 as a first row and empid =2 as a second row.
Because the clustered index has to maintain the physical order of the stored records according to the indexed column, and so it sorts the records based on EmpId in ascending order as table Emp has a clustered index on EmpId column.
Lets insert a records in table Emp, also note that the values for Empid columns are not in sequential order, which we are purposely inserting a records in non sequential order or you can say random order of the values in the EmpId column to see the behaviour of clustered index.
INSERT INTO dbo.Emp (EmpId, Name, Department, DOB) VALUES (4, 'Ajit Singh', 'CS', '12/06/1989'), (3, 'Mohit Chabara', 'ES', '06/28/1990'), (6, 'Sujoy Gosh', 'CS', '04/21/1988'), (2, 'Manjit Kumar', 'ES', '03/20/1991'), (1, 'suraj Kumar', 'CS', '08/30/1992'), (5, 'Neeraj Rawat', 'ES', '07/05/1989')
Lets fetch the records from table Emp.
SELECT * FROM dbo.Emp
You can see, the records are returned in sorted order of EmpId column because the clustered index determine the order in which data rows are physically stored in a table.
So far we have seen, a clustered index is created automatically when you create a primary key on table column.
Creating a clustered index using CREATE CLUSTERED INDEXÂ statement
Lets create a clustered index on table Department column using CREATE CLUSTERED INDEX statement.
As you already know that a table can have only one clustered index, which a table Emp already have on EmpId column.
Lets see what happens, if you try to create another clustered index on table Emp.
CREATE CLUSTERED INDEX Idx_Dpt ON dbo.Emp (Department)
You can see, it returns an error saying “Cannot create more than one clustered index on table ‘dbo.Emp’.
Drop the existing clustered index ‘PK__Emp__AF2DBB99C5D63CE3’ before creating another”.
That means you have to first drop an existing clustered index first then only you can create a new clustered index.
Lets quickly drop an existing index using object explorer.
Right click on Index PK__Emp__AF2DBB99C5D63CE3 inside indexes folder and then click on delete in context menu as shown below.
After that, Delete Object dialog box opens, click on OK button.
Once you click on OK, it deletes an existing clustered index as shown below.
Lets create a clustered index on Department column using a CREATE CLUSTERED INDEX statement.
For this you can run above script again.
CREATE CLUSTERED INDEX Idx_Dpt ON dbo.Emp (Department)
As you can see, clustered index is created on Department column, that you can quickly check using below SQL Server system stored procedure.
EXECUTE sp_helpindex Emp
Lets select the records from table.
You can see, clustered index on department column sorts the department name in ascending order.
You can also specify the sorting order while creating a clustered index as shown below.
Following clustered index on department column will sort the department records in descending order.
CREATE CLUSTERED INDEX Idx_Dpt ON dbo.Emp (Department DESC)
You can also create a one clustered index on multiple columns that is called a composite clustered index.
Suppose you want to create a clustered index on EmpId and Department column then you can use following statement.
CREATE CLUSTERED INDEX Idx_EmpId_Dpt ON dbo.Emp (EmpId ASC, Department DESC)
The above clustered index first sorts all the records in the ascending order of the EmpId. If EmpId is same for two or more records, then the records are sorted in the descending order of the values in their Department column.
NON-CLUSTERED INDEX
A non-clustered index doesn’t sort the physical data inside the table, which means non-clustered index
A non-clustered index contains the index key values and row locators that point to the storage location of the table data.
A table can have more than one non-clustered index.
Basically, non-clustered indexes are created to improve the performance of frequently used queries which are not covered by the clustered index or to locate rows in a table without a clustered index (called a heap).
Lets create non-clustered index on Name column in table Emp.
CREATE NONCLUSTERED INDEX idx_name ON dbo.Emp(Name DESC)
You can see, a non-clustered index in created on Name column. Lets check whether a non-clustered index is created or not.
As you can see a non-clustered index is created on Name column.
EXECUTE sp_helpindex Emp
This non-clustered index sorts the records in table by name in ascending order.
As we know that a non-clustered index doesn’t sort the physical data inside the table, therefore data is stored in one place and the index is another place.
In case if table has clustered index then the records in table will be sorted by a clustered index, and the non-clustered index will be sorted according to its definition and will be stored separately from the table.
Lets select the records from table Emp.
As you can see, data in table is sorted based on existing clustered index that you have created on Department column.
But the data for non-clustered index will be sorted by name in descending order and will be stored separately from the table which will look similar to as shown below.
Also Read..
