An index can be best described as a pointer to data in a table. An index in a database is very similar to an index in the back of a book or the phone book. Indexes are created on the table or the view.

If index is not available on a table then database engine has to go through all the records in the table to achieve the desired result.This process is called table scanning and it is very slow because of that it impacts the performance of the server.

On the other hand, if you create an index, then the database first goes to that index and then retrieves the corresponding table record directly.

difference between Clustered and Non-Clustered Indexes in SQL Server

Clustered Index :-

Clustered indexes sort the data rows in the table or view based on their key values, as such there can be only one clustered index per table.A clustered index defines the order in which data is physically stored in a table. When a table does not have an index, it is referred to as a heap. When you create a clustered index, it does not require any additional disk space.In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

Create a “PhoneBook” table inside “DemoDB” by executing the following script:-

CREATE TABLE [dbo].[PhoneBook] ([PhoneBookID] [int] PRIMARY KEY, [lname] [varchar](40) NOT NULL, [fname] [varchar](40) NOT NULL, [phone] [varchar](50) NOT NULL )

If you notice here in the “PhoneBook” table we have set primary key constraint on the “PhoneBookID” column. This automatically creates a clustered index on the “PhoneBookID” column

To find out all the indexes on a particular table through “sp_helpindex” stored procedure.

Through below query we are able to find the indexes created on PhoneBook table

EXECUTE sp_helpindex PhoneBook

The above script return below result:-

This clustered index stores the record in the PHONEBOOK table in the ascending order of the “PhoneBookID” .To check this ordering in action, execute the below script:-

Insert into PHONEBOOK values (1005,'Abba','Sara','555-1212'), (1001,'Turner','Mike','805-555-1212'), (1004,'Jobs','Steve','805-333-1212'), (1002,'Bill','Gets','905-555-2124'), (1003,'Johnson','Abella','801-555-1212')

Here we have inserted 5 records in random order But because of the default clustered index on the PhoneBookID column, the records are physically stored in the ascending order of the values in the “PhoneBookID” column.

Going to Execute the SELECT statement to retrieve the records from the PHONEBOOK table.

SELECT * FROM PHONEBOOK

After execution of Select Statement The records will be retrieved in the following order:

Creating Custom Clustered Index

You can also create your own custom Clustered Index but to create a new clustered index on a table first you have to delete the previous index from the table because you can create only one Clustered Index on a table. To create a new clustered Index, execute the below script:-

CREATE CLUSTERED INDEX IX_PHONEBOOK_fname_phone ON student(fname ASC, phone DESC)

We can create Clustered Index With “CLUSTERED” Keyword before “INDEX”.

Above script creates a clustered index named “IX_PHONEBOOK_fname_phone” on the PHONEBOOK table. This index is created on the “fname” and “phone” columns. An index that is created on more than one column is called “composite index”.

The above composite clustered index first sorts all the records in the ascending order of the fname column. If fname column is same for two or more than two records then the records are sorted in the descending order of the values in their “phone” column.

Non-Clustered Indexes:-

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place.

A Non-clustered index have a structure separate from the data rows, much like the index in back of a book, and as such does require disk space, as it’s a separate object.

A non-clustered index contains a pointer to the data row that contains the key value. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

This is similar to a textbook where the book content is located in one place and the index is located in another. we can create more than one non-clustered index per table.

Note:- non-clustered indexes are slower than clustered indexes.

Creating a Non-Clustered Index

We can create a non-clustered index is with keyword “NONCLUSTERED” is used instead of “CLUSTERED”.

we can create a new NONCLUSTERED Index through following script:-

CREATE NONCLUSTERED INDEX IX_PHONEBOOK_fname ON PHONEBOOK(fname ASC)

The above script creates a non-clustered index on the “fname” column of the PHONEBOOK table.

The index sorts by fname in ascending order

Difference between Clustered and Non-Clustered Indexes Conclusion:-