Questions

Clustered index vs Non clustered index

Advertisements

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.

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. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

6 Comments

  • Anonymous

    hi

    Nice explanation…

    But, In Clustered Index (4th Point) – has some Changes to be done…

    CLUSTERED Index will be created by default When you create a PRIMARY KEY on that table – "Only If the table already doesn't have CLUSTERED INDEX".

    If the table already have any CLUSTERED Index then, The PRIMARY KEY will be created as a NON-CLUSTERED Primary….

  • Penni Lockerby

    I should say, youve got one of the most effective blogs Ive noticed in a long time. What I wouldnt give to be able to produce a blog thats as interesting as this. I guess Ill just need to maintain reading yours and hope that one day I can write on a topic with as substantially expertise as youve got on this one!

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 6 =

hacklink panel hacklink al hacklink sollet hdxvipizle resim yükleme onwin venüsbet ai nude 1xbet güncel giriş eskort yeşilköy cami avizesi seo paneli soma kömür bahis forum backlink al pgslot สล็อตเวตรง holiganbetvds
hack forum hacklink panel hacklink hacklink cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı saricahali.com.tr cami halısı cami halısı cami halısı cami halısı cami avizesi cami avizesi cami süpürgesi cami süpürgesi cami ısıtma cami ısıtma evden eve nakliyat ofis taşıma seo hizmeti Onwin Rulet Casino Slot Oyna Bahigo 1xbet Deneme bonusu Bahis siteleri Maç özetleri Bahsegel Canlı Casino Siteleri Sweet Bonanza Blackjack Casino Rulet Oyna Canlı Casino takipçi satın al exeboost.com smm panel santenette.com google.com.tr m