5

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

|improve this question|||||
  • If you don't anticipate these tables growing a great deal larger, what do you hope to gain by adding clustered indexes? – Erik Darling May 17 '17 at 19:16
  • I have no idea! I'm really basing this on a notion that without clustering you're stuck with scans instead of seeks, and seeks are good and scans are bad. My formal training is in website development; DBA learning has been along the way and as required. – mlhDev May 17 '17 at 19:20
  • 1
    If the tables without clustered indexes are all relatively small, you likely won't see a measurable difference by adding them. I usually still cluster mine, though, just in case they grow. – BradC May 17 '17 at 19:22
  • 1
    @mlhDev oh dear, no. Especially for 500 row tables. If these are your biggest performance problem or concern, you don't need DBA training, you need a vacation. – Erik Darling May 17 '17 at 19:25
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....

|improve this answer|||||

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.