Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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.

Comments

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

Loading comments...