Heaps are Lurking in your Database…

And you know that’s a bad thing™. At least, most of the time. Some people swear by ’em, and for certain requirements a heap is the best thing since sliced bread.


     Heaps ‘o trash!

For most scenarios you don’t want OLTP data stored in heaps for all kinds of reasons. Anyway, if you suspect you might have a database that’s been questionably designed, and need a list of tables that have been built without a clustered index, you can use this quick and dirty ole query to identify ’em:

The query returns two columns; the name of the table, and a comma-separated list of indexes defined on the table, just in case there are some. You never know, you might have a table with a non-clustered primary key. And by all means, you might actually need a table with a non-clustered primary key. If you clearly understand the reasons you might want a primary key index that is not clustered, then be my guest.

The query above can be extended using a little dynamic T-SQL to show heaps in all the databases on an instance:

Let me know if you think Heaps shouldn’t be persecuted so much!

This post is part of our series on troubleshooting.