Clustered and Non-clustered Indexes in SQL – Database

Before understanding the difference between Clustered and Non-clustered Indexes, we will understand the concept of Index in SQL.

Difference between Clustered and Non-clustered Indexes in SQL - Database

What is index in SQL Database?
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

When should indexes be avoided?
Although indexes are intended to enhance a database’s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

1. Indexes should not be used on small tables.
2. Tables that have frequent, large batch update or insert operations.
3. Indexes should not be used on columns that contain a high number of NULL values.
4. Columns that are frequently manipulated should not be indexed.

Now, we will see the types of SQL Server Indexes. There can be two kinds on indexes in relational database.

1. Clustered Index
2. Non-clustered indexes

Clustered Index
Clustered index determines physical sorting order of rows in a table similar to entries on yellow pages which are sorted on alphabetical order. Suppose you have a table Employee, which contains emp_id as primary key then clustered index which is created on primary key will sort the Employee table as per emp_id.

Non-clustered Index
Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

We will see some more difference between clustered and non-clustered index in point format.
1) Clustered Index physically sort all rows while Non-clustered Index doesn’t.
2) In SQL one table can only have one Clustered  Index but there is no such restriction on Non-Clustered Index.
3) In many relational databases Clustered Index is automatically created on primary key column.
4) Clustered Index is faster to read than non clustered as data is physically stored in index order.
5) Non-clustered Index is quicker for insert and update operations than a clustered index.
6) The leaf nodes of a clustered index contain the data pages while the leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
7) A Primary Key constraint creates a Clustered Index by default while a Unique Key constraint created a Non-clustered Index by default.
8) A Clustered Index always has Index Id of 1 while non-clustered Indexes have Index Id > 1.

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

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

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.