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.

29 thoughts on “Difference between Clustered and Non-Clustered Index Data Structures

  1. Michale Boen

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

Leave a Reply to Zimriel Cancel reply

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