When you might not want to use a clustered index

In some scenarios, it might be beneficial to not have a clustered index on a table.

E.T.L. staging tables is one scenario that you might not want to have a clustered index. We’re loading into a data warehouse a few times a day, and that could be hundreds of thousands of records at a time, depending on the nature of your business. And in some scenarios, we want to see that just loaded into a flat table where we’re going to do some transformations on it, and then load it into our permanent table. You might not want to use a clustered index because you’ll get some benefit just loading that data into the unsorted pages, kind of in parallel. 

If you’re just doing a straight load into the table, you may see better performance with a heap, and that’s something to keep in mind for staging tables in your data warehouse.

Another use case would be auditing and logging. So if you’re audit logging within your database, this can be a pretty good use case for using a heap, ’cause you’re only ever really writing to that table.