SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Clustered index vs Non clustered index

Clustered index vs Non clustered index structure

Clustered and Non clustered indexes are stored in a 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 at the 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 have clustered index it’s called “Heap”


Non Clustered Index

  • Non clustered indexes are separate storage. (I.e. original table and an index stored separately)

 

  • The 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.

SQL Server Blog Forum

My name is Muthukkumaran Kaliyamoorthy and I am living in India (Chennai). I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server, and I’m specialized in Administration and Performance tuning.

Comments

Leave a comment on the original post [www.sqlserverblogforum.com, opens in a new window]

Loading comments...