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/N||Key||Clustered index||Non clustered index|
|1||BASIC||The basic is created on the primary key.||The basic can be created on primary or any other keys.|
|2||ORDERING||Data are physically stored according to order.||Data are not affected by order here.|
|3||NUMBER INDEX||Only a(one) clustered index can be contained in a table.||More than one non-clustered index can be contained in table.|
|4||SPACE||Extra spaces are not required for storing logical structures.||Extra spaces are required storing logical structures|
|5 ||PERFORMANCE||Clustered index performs faster data retrival than non-clustered index.||Non Clustered index performs data update faster than clustered index.|