Steve Jones - SSC Editor (5/24/2016)
vliet (5/24/2016)
Well, heaps do have a slight advantage in certain cases. Because SQL Server ...I really dislike opening statements like this. You are almost implying that someone reading this should really consider heaps every time, or at least, often.
Every table should have a clustered index. Start with that. If you find you have a valid, rational, backed up by data reason, then you can use a heap. However, don't consider heaps from the start.
I guess the exception to that rule is if the code you write will always do a table scan because the whole table is always everything you want. This is particularly true with import staging tables. When I wrote part 1 of "Hierarchies on Steroids", I actually documented the fact that you didn't want to build a clustered index until after the Nested Sets hierarchy had been built and was ready for use because it would actually slow the build down... by quite a bit, IIRC.
--Jeff Moden
Change is inevitable... Change for the better is not.