Labels

Monday, 21 December 2015

 SQL Server - Differences between Clustered Indexes and Non-Clustered Indexes?


The below are the major differences between Clustered and Non-Clustered Indexes .

S.No

Clustered Indexes

Non-Clustered Indexes

1

Clustered Index  reorders the physical storage of records in the able It sorts and maintain a separate storage

2

There can be only one Clustered index per table


We can have 249(before 2008) ,999 (from 2008) non-clustered indexes in a table

3

The leaf nodes contain data

The leaf node contains pointer to data

4



To create clustered index SQLServer required more memory because the leaf pages in the tree structure will maintain actual data .

To create non-clustered index Sql server requires less memory because the leaf pages will contain pointers to actual data.

5

By using clustered index retrieving data is more faster,when we compare with non-clustered index. By using non-clustered index retrieving data is
slower than clustered index.

1 comment: