Replacing PK's in Existing SQL DB Tables

Question

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

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