Press "Enter" to skip to content

Difference between Clustered and Non-Clustered Index Data Structures

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a ?heap?. As you would expect, the data we will insert into the table will be returned in the same order. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don’t need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.

28 Comments

  1. Benedict
    Benedict January 10, 2012

    thanks fro the help i rely appriciate big up

  2. Priya PAdhy
    Priya PAdhy February 16, 2012

    very nice

  3. dheep
    dheep March 21, 2012

    hey thanks :)

  4. Michale Boen
    Michale Boen March 31, 2012

    hello there and thank you for your information ? I’ve definitely picked up something new from right here. I did however expertise several technical points using this website, since I experienced to reload the website a lot of times previous to I could get it to load properly. I had been wondering if your web hosting is OK? Not that I am complaining, but sluggish loading instances times will often affect your placement in google and could damage your high-quality score if advertising and marketing with Adwords. Well I am adding this RSS to my email and can look out for much more of your respective exciting content. Ensure that you update this again soon..

  5. Kumar Vaddadi
    Kumar Vaddadi May 16, 2012

    good informative, I learned new things about clustered-index

  6. mohsendokht
    mohsendokht May 23, 2012

    thanks for your nice description. that is simple and complete.

  7. ram
    ram June 15, 2012

    Good:)

  8. Kapil
    Kapil June 28, 2012

    Good Explaination

  9. Bhuvnesh
    Bhuvnesh July 2, 2012

    Even clustered index does not guarantee the order of records while extracting records.

  10. Jayaprakash
    Jayaprakash July 9, 2012

    Very good and clear explanation.

  11. Nitiratna
    Nitiratna July 11, 2012

    Thats realy nice.

    Take a full mug man.

  12. Prasad
    Prasad August 1, 2012

    Very good and clear explanation.

    Thanks…:)

  13. sambit
    sambit August 3, 2012

    thanks a lot for this ..

  14. Lost Prophet!
    Lost Prophet! August 20, 2012

    Thnx Man! It helps a lot

  15. ajit chormale
    ajit chormale September 2, 2012

    Its very good and clear explanation in simple words.
    Thanks..

  16. saritha
    saritha September 22, 2012

    Good Work Keep it up

  17. sachin
    sachin September 26, 2012

    short and sweet!!
    Thanks. :)

  18. mohan yadav
    mohan yadav September 26, 2012

    thanks bro

  19. pranjal mishra
    pranjal mishra November 1, 2012

    well explained.
    got my solution

  20. Sachin Patil
    Sachin Patil December 4, 2012

    good

  21. rajyalakshmi
    rajyalakshmi December 30, 2012

    good explanation

  22. Zimriel
    Zimriel January 10, 2013

    Coffee? Hell, for this handy guide I’ll buy you any beverage you want

  23. Raj
    Raj January 24, 2013

    Nice Explanation. Give more like this ….

  24. Tushar Ghodake
    Tushar Ghodake January 24, 2013

    Perfect!!!

  25. Priya
    Priya January 28, 2013

    Its an awesome explanation..Thankyou so much

  26. Dhanraj
    Dhanraj March 13, 2013

    Very nice.. Have a coffee with me.

  27. susmita saha
    susmita saha March 16, 2013

    perfect answer

  28. Hasan
    Hasan March 17, 2013

    Needed more details….. with figure…… theory is not good enough…….

Leave a Reply to ram Cancel reply

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