Adding a clustered index to a heap table
Would you agree this is the right thing to do?


Yes, especially if (1) the table is looked up using the Primary Key, and (2) you are noticing performance issues.

Note that if the table is primarily looked up using a set of data other than the PK (Example: Order Lines may be looked up by the OrderID and sorted by the OrderLineId), then you may consider a clustered index based on the most common lookup(s), especially if they select sequences of rows. For example, on the Order Lines example above, having a Clustered index like:

CREATE CLUSTERED INDEX Idx_OrderID_OrderLineId
ON OrderLines (OrderId, OrderLineId)


would be optimal for retrieving all of the order lines for a given order.

Also, there are cases where having a non-clustered covering index may be optimal. You will need to keep an eye on performance whatever you do with these tables.


  If so what are the best practices for adding a clustered index to this
  table, i assume i will have to remove the existing non clustered index
  on the primary key (and any foreign keys referencing it) and then add
  the clustered index and recreate the foreign keys?  Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?


Best practices depends on the answers to the above questions. If you actually want a clustered PK (because that is how the table is commonly referenced, or because there is no clear clusterable index that could be created), then you will need to perform all of the steps you specify above. If you decide to create a clustered index that is identical to your PK (and yes, people do this... ) you won't need to do all these steps. And if you decide your optimum clustered index is some combination of fields not identical to the PK, then you will not need to do all of these steps either.

If you do replace the PK with a clustered PK, then there are scripts that can help with identifying the foreign keys. I tend to use the View Dependencies functionality inside to SSMS to identify possible locations for FKs to my table, then manually script them out and drop them myself (because I had some bad experiences with scripts that should handle FKs), so I can't recommend any specific choices right now.


  Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?


Not really, no.