1

Right now I have a DB where the PK's are int IDENTITY. I recently, in the last year, was tasked with adding these to a Replication Topology. This has worked out quite well with the exception of the IDENTITY fields.

I want to explore my options for changing or replacing them with a uniqeidentifier(GUID).
Is it feasible to insert a NEW PK column?
Would I be better of just increasing it to a big int?

Anything else I should consider?

To elaborate on WHY I want to do do this:

It is my understanding that when Replication encounters an IDENTITY Column it sets aside an Identity Range, say 1-1000(default), for each subscriber to ensure an Unique INT for that column. The more subscribers you have the bigger issue it can become. This leads to the Identity Range Check Constraint errors we keep getting.

Thanks

Refracted Paladin
  • 12,096
  • 33
  • 123
  • 233
  • Can't you just add a new GUID column to be used in replication? Just add - not replace - might be easier (if it's feasible) – marc_s Oct 21 '10 at 16:33
  • That HAS to be done it order to use Replication and is done. The issue I am trying to address is with 'identity range check constraints' which is a direct correlation to how Replication deals with Identity Fields. – Refracted Paladin Oct 21 '10 at 16:34
  • So in that case, I would *add* a new GUID field, and make it the PK - but a NONCLUSTERED PK - and leave the INT IDENTITY in place as the clustering key on the table. Your performance might suffer significantly otherwise... – marc_s Oct 21 '10 at 16:36
  • @marc_s - Doesn't clustering on GUIDs help with hotspots though if you are updating frequently? – JNK Oct 21 '10 at 16:38
  • @JNK: hotspots aren't a probably anymore since SQL SErver v7 - but clustering on a GUID gives you horrible index fragmentation and hurts performance all around.... – marc_s Oct 21 '10 at 16:41
  • @marc_s - I will have to check whether having an IDENTITY field at all is a problem with Replication or only if it is also the PK. I was under the assumption that having it at all was the issue. – Refracted Paladin Oct 21 '10 at 16:47

1 Answers1

1

If you really must remove the INT IDENTITY column, then you'd have to do these steps (more or less):

  • create the new GUID column in your table and fill it with values
  • identify all foreign key relationships referencing that table and making a note of those (e.g. store their CREATE scripts on disk or something)
  • add a new GUID reference field to all the referencing tables
  • fill those values based on the INT reference field you already have
  • drop all the FK references to your table
  • drop the INT IDENTITY PK on your table
  • make the new GUID column your PK
  • drop the old INT reference columns from all referencing tables
  • re-create all foreign key references using the new GUID reference column

I hope that should do the trick.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459