Moving from ints to GUIDs as primary keys

Question

I use several referenced tables with integer primary keys. Now I want to change ints to GUIDs leaving all references intact. What is the easiest way to do it?

Thank you!

Addition

I do understand the process in general, so I need more detailed advices, for example, how to fill new GUID column. Using default value newid() is correct, but what for already existing rows?

Answer 1

• Create a new column for the guid value in the master table. Use the uniqueidentifier data type, make it not null with a newid() default so all existing rows will be populated.
• Create new uniqueidentifier columns in the child tables.
• Run update statements to build the guild relationships using the exisitng int relationships to reference the entities.
• Drop the original int columns.

In addition, leave some space in your data/index pages (specify fillfactor < 100) as guids are not sequential like int identity columns are. This means inserts can be anywhere in the data range and will cause page splits if your pages are 100% full.

Answer 2

Firstly: Dear God why?!?!?

Secondly, you're going to have to add the GUID column to all your tables first, then populate them based on the int value. Once done you can set the GUIDs to primary/foreign keys then drop the int columns.

To update the value you'd do something like

1. Set the new GUIDs in the primary key table
2. Run this:

.

UPDATE foreignTable f
SET f.guidCol = p.guidCol
FROM primaryTable p
WHERE p.intCol = f.intCol