I have a database that has multiple tables without any clustered index. All that I have found are small (< 500 rows). Specifically, PRIMARY KEY NONCLUSTERED with no other indexes at all.

At a very high level I understand the performance limitation of not having a clustered index. Perhaps more so I understand it isn't best practice, but before I aggressively start converting non-clustered primary keys to clustered ones, are there any caveats I should be aware of when converting these indexes?

The only one I can think of would be the time it would take to update an index on a large table, but again these are relatively small. Perhaps files & partitions, but I only have a single partition.

For reference, this is a 2008-compatible SQL Server database running on SQL Server 2016. This is in our primary OLTP system (these table are not involved in warehousing & ETL).


1 Answer 1


Back in the old days (as best I recall), tables that small theoretically didn't need indexes at all. However, clustered indexes have a couple of other advantages.

  • Better space management - tables without a clustered index basically don't release disk space, even when rows are deleted.
  • Less space needed - Since a clustered index is built into the table's structure, replacing a non-clustered index with a clustered index usually frees up much of the space the non-clustered index took up.

And, of course - with 500 row tables, both of those things should be too little to really notice.

On the con side, it's possible that your non-clustered index managed to be a covering index for some queries, so the actual table didn't need to be touched to get results. So, some queries could actually be slower.

And, of course - with 500 row tables, that should be too little to really notice.

I agree with BradC's comment, however - if the table does start to grow much bigger than expected, I'd rather have a clustered index on it than not. And, if you enforce a standard of putting a clustered index on every table, then you're less likely to wind up with a heap with 400MB of data, and 300MB of unused space....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.