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

     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.