SQL Server – Difference Between Clustered and Non-Clustered Indexes

  • 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:-

 

  • only one clustered index per table where as you can have more than one non clustered index per table.
  • cluster index faster than non-cluster index because the clustered index has to refer back to the table, if the selected column is not present in the index
  • cluster index determines the storage order of row in a table hence does not require additional disk space but a non-clustered index is stored separately from the table,additional storage is required
  • A clustered index physically sort all rows while Non clustered Index doesn’t
  • Clustered Index is automatically created on the primary key column
  • Non clustered indexes do only consist of index key columns, pointers into clustered indexes or the specific row in a specific page, and if you so want included columns. They are redundant to the table, but points to where the row is stored.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *