Jeremiah Peschka looks at a scenario in which a heap might be superior to a clustered index:
In this case, we have to assume that
Event IDs may be coming from anywhere and, as such, may not arrive in order. Even though we’re largely appending to the table, we may not be appending in a strict order. Using a clustered index to support the table isn’t the best option in this case – data will be inserted somewhat randomly. We’ll spend maintenance cycles defragmenting this data.Another downside to this approach is that data is largely queried by
Owner ID. These aren’t unique, and oneOwner IDcould have many events or only a few events. To support our querying pattern we need to create a multi-column clustering key or create an index to support querying patterns.
This result is not intuitive to me, and I recommend reading the whole thing.