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).
Best Answer
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.
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....