SQL SERVER - Types Of Indexes

Index is that which can be used for uniqueness and Fast Data Retrieval

There are two types of indexes. They are :

1.Clustered index
2. Non Clustered index

Clustered index is that in which all the records are arranged in sorted order on the index key where as, in case Of Non Clustered index no records will be in sorted order.

In case of Clustered index as the data is in the Sorted Order and there will be minimal effort to retrieve the data. But in case of Non clustered index maximum error must be kept to retrieve data with Range Search.

In Clustered index, the Actual data is available at the end of the leaf node. Hence there will be only one Clustered index. In Non Clustered index the address will be stored in the leaf nodes. Hence there may be many number of Non Clustered indexes.

The number of Non Clustered indexes in a Table are 249 while the number of Clustered index is only One.