I stumbled across this clustered index test from Hugo Kornelis recently from a post in the forums. I think I’d read it before, but it was surprising to me again this week as I read through it.
The test seems to show that if you are changing data over time with UPDATEs on a table, which is a heap, then you can not only fragment the table, but you can dramatically impact performance. His test, which seems reasonable, shows that you can end up with 10 times the rows being read.
I’m not sure that this is representative and your mileage may vary. You could end up with more or less scans, but what if you had double the number of rows read? That’s still a substantial impact to performance in your application. You might not notice if it you don’t run queries that force table scans often, but based on the quality of code I see people asking about on the forums, that’s not likely.
Instead, add a clustered index. The original post was from someone worried about fragmentation on a series of tables for a third party application and they couldn’t “change anything”. To me that’s dumb. Adding a clustered index, in the hope of improving performance, isn’t really a big change. And it can easily be undone if it worsens performance in that application.
It’s not likely, but it could happen. However a third party that doesn’t want to allow a clustered index to be added to tables in their application because it might cause issues is a seriously ignorant company on how a clustered index works.