SQL Server - Clustered Indexes and Non Clustered Indexes

To understand the concept of Clustered and Non Clustered indexes, it is important to understand the definition and functionalities of indexes first. An Index is a structure related to a view or table that speeds up the processes by quick retrieval of integrated rows from view or table.

There are two types of indexes exists in view or table. These are:

1. Clustered Indexes
2. Non-Clustered Indexes

An Overview on Clustered Indexes:

The data inserted by users in the tables is physically rearranged by Clustered Indexes. In a table, the physical arrangement and the order of data is determined by the clustered table. On disk media, the clustered index is arranged through a proper storage paradigm.

In clustered indexes, data access is a complex task comparative to accessing data in data pages. When group of similar values is been entered in the database table, this clustering of items is termed as Clustered Index.

In the view or table, the data rows are sorted and stored on the basis of the key values by Clustered Indexes. Only one clustered index exists in a table because the data rows can only be sorted and rearranged in one order.

The only instance when rows are sorted and stored in a table is when the database table holds a clustered index. The instance when the database table contains clustered index is known as clustered table. On the contrary, when no clustered index exits in a table and the rows are saved in an unarranged format, this condition is termed as ‘Heap’.

Standard statements i.e., SELECT, INSERT, UPDATE, DELETE can be used for accessing data in a table with clustered index. The only requisite for running all these operations on the table is that the data in the SQL Server must be stored as per parameters set by clustered indexes.

Clustered Index can be used for:

1. Column containing large volume of different values.
2. Columns that are viewed in sequence.
3. Queries returning wide result set.

Clustered Index must not be used for queries that involve frequent changes as well as on the wide keys.

Associated Limitations & Restrictions:

While creating clustered indexes, it requires that the disk space associated to both source (old) and target (new) structures must be allocated within their respective file groups and files.

When clustered index is built along with the non clustered index, the non clustered indexes requires to be rebuilt so that they can hold clustered key value in place of row identifier (RID).

For more details about how to create clustered indexes visit

An Overview on Non-Clustered Indexes:

In non-clustered environment, the structure of the indexes varies from the structure of the data rows. Based on the non-structured keys, the data rows are not stored or sorted in an arranged manner. In this structure, the leaf layer is not consisted of the data pages and in fact the leaf node contains the index row.

The overall flow of processes seems to be quite puzzling in this environment. A non clustered key value exists in non clustered index and every key value points towards the data row that contains that particular key value.

In non-clustered index, pointer from index row towards the data row is referred to as the row locator. The formation of the row locator depends upon the storage of data pages that decides if it is been stored in heap or a clustered table.

In SQL Server environment, indexes are not unique and the same key can be shared by multiple rows. On the contrary, the clustered as well as the non clustered indexes can be unique and this further implies that two different rows cannot share the same index key values.

The non-clustered pages are considered for:

1. Column containing wide volume of different values.
2. Columns that involved in frequent search operation of a query and return accurate results.
3. Queries returning smaller result sets.

Conclusion:

Both clustered and non clustered indexes exist with indifferent approach and merits. In SQL Server environment, both of these two concepts are implemented under set parameters. Creating non clustered indexes on tables sometimes proved to be beneficial as they appear identical to that of clustered indexes.