Heaps vs Clustered Tables Explained

Chronicles of DB Indexes - Part 10

I used to be confused with the whole "Clustered vs Nonclustered vs Heaps" ordeal. But it turns out it's EXTREMELY SIMPLE!

Do you want your data to be stored in specific order? Use Clustering Index!

Don't care about the order and just want to store as much data as fast as possible? Use Heaps (i.e. don't specify Clustering Index!)

It's that simple, really!

Heaps are really amazing for huge amounts of data where you don't care about the order itself. It takes NO time to store the data (it just gets appended to first available slot) and you can just load it in the order it was added in (IAM allocation order! Remember that one?)

On the other hand, if your data follows some natural order and you want to be able to retrieve data back in that order without wasting CPU cycles on sorting - add Clustered Index on the column that dictates the order!

Additionally, specifying Clustered Index means that your data pages will be organized in a B+Tree! And the leaf pages ARE YOUR DATA :) It's beautiful!

In the following articles we'll be talking more about Hashes and Secondary (non-clustering) indexes. But until then, if you like this content, please spare a LIKE :) Takes no time from you, but provides a gigantic THANK YOU to me!

Have fun!