Clustered indexes VS Non-clustered indexes

Clustered indexes VS Non-clustered indexes

Clustered indexes:

An index is strictly associated with one table or view and  contains the unique key in each row of that table.

Benefits

• Allow to fetch data randomly
• Contains less space
• Manage data with in the table so there is no need of reference table
• Retrieve data more quickly
• Enhance the performance

Limits

• Not useful if user wants to insert the  record on the same index recursively

Non-clustered indexes:

It has a structure separated from data row contains the non clustered index key values and each key value entry has a pointer to the data row that contains the key value.

Benefits

•   Allow to fetch data randomly
• Useful if user wants to insert /update the  record on the same index recursively

Limits

Requires more space

Record retrieving speed is slow

Conclusion: Clustered indexing is more useful technique if record is not required to update or insert recursively on an index

By Hafiz Muhammad Umar Hayat