ProgrammerGuide.Net | Step by Step Programmer Guide

Difference Between Clustered And Non Clustered Index

An index is a lookup table that is used by the database to enhance data retrieval performance timing. in this context, keys are stored in a B-Tree structure which helps and enhances the Sql server to locate the row(s) associated with key value faster and easily. To create an index, primary key and unique constraint must be defined in the table. Once its defined, an index will be automatically created.

There are two types of index :

CLUSTERED INDEX: In the clustered index, a user creates a table using primary key contraints and when this is done, a clustered index is created automatically by the database engine.

NON CLUSTERED INDEX: In the non-clustered index, a table is created using a UNIQUE contraints and when this is done, a non-clustered index will be created automatically by the database engine. A non-clustered index key values are contained in the non-clustered table and each of the non key values entry has a pointer; they directly points to the data row that contains the key value.

S/NKeyClustered indexNon clustered index
1 BASICThe basic is created on the primary key.The basic can be created on primary or any other keys.
2 ORDERINGData are physically stored according to order.Data are not affected by order here.
3 NUMBER INDEXOnly a(one) clustered index can be contained in a table.More than one non-clustered index can be contained in table.
4 SPACEExtra spaces are not required for storing logical structures.Extra spaces are required storing logical structures
PERFORMANCEClustered index performs faster data retrival than non-clustered index.Non Clustered index performs data update faster than clustered index.
Please click here for related products on Amazon!


Add comment

Want to Check Celebrities News?