1

I am working on a legacy database. The primary key is clustered varchar(128) and the table contains a high volume of data.

Does it necessarily help performance if I change the clustered PK to nonclustered?

Changing the table to a heap would at least it prevent more data fragmentation after inserting any new rows (to the best of my knowledge - I am not a DBA). I also have similar problem with uniqueidentifier primary keys here.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Mori
  • 163
  • 4
  • 1
    uniqueidentifiers are awful clustering keys. I'd recommend having a clustered index rather than leaving it as a heap, are there any integer columns your could use instead?

    Having no clustered index will still result in fragmentation, as the only way a base table can be defragged is for its clustered index to be rebuilt (or created).

     – Thomas Pullen Apr 21 '16 at 15:36
  • 1
    In order to answer this question, we're going to need more information about the table and it's other indexes. And how many rows. There are good reasons for having a clustered index, but you need to choose it carefully. A good clustered index is generally narrow, unique, and ever increasing. So if you stop clustering on this column, think about what column(s) you will cluster on. A good clustered index will help your nonclustered indexes perform. – DatumPoint Apr 21 '16 at 15:57
  • @ThomasPullen it's not only one table. There are many tables in this db which have similar issue. I am gonna investigate a bit and find good candidates for being clustered index in each table. – Mori Apr 21 '16 at 16:05

0 Answers0