Saturday, October 1, 2011

Clustered Index vs Non Clustered Index

Clustered and Non clustered indexes are stored in B-Tree Structure.
Clustered Index
  • Clustered index enforce the logical order. (Misconception: Clustered index does not enforce the physical order)
  • A table has only one clustered index because, the original table stored in leaf level of the clustered index (Data pages).
  • When you create a primary key by default clustered index will be created internally.(If the table has clustered index already then the non clustered index will be created internally)
  • If the table does not has clustered index it’s called “Heap”
Non Clustered Index
  • Non clustered indexes are separate storage. (I.e. original table and an index stored separately)
  • Non clustered index does not enforce the logical order. The physical order of the rows is not the same as the index order.
  • A table has 999 non clustered indexes in sql-2008, 249 non clustered indexes prior to 2008. 
  • When you create a unique key by default non clustered index will be created internally.

No comments:

Post a Comment