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

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

Answer 2

Yeah, I'm with Glenn... I was actually hesitating on posting the same thing before he posted it....

Why would you not want an auto increment int primary key separate from your GUID? it's a lot more flexible, and you can just have the GUID column indexed so you have good performance on your queries...

---

As for the flexibility, I like to keep my id's as autoincrement ints because then the other seemingly unique and primary-key worthy item can change.

A great case of the flexibility is if you use usernames as a primary key. Even if they are unique, it is nice to be able to change them. What if users use an email address as their username? Being able to change the username and have it not affect all your queries is a big plus, and I suspect the same could be true with your GUIDs....

Answer 3

I think, you must do it manualy. Or you can write some utility for it. The scenario should be:

• Duplicate the "int" PK/FK columns with new "guid" columns.
• Generates new values for "guid" PK columns.
• Update values in "guid" FK columns with specified values ( you find the records via "int" PK ).
• Remove references ( relations ) with "int" PK/FK columns.
• Create similar references ( relations ) with "guid" PK/FK columns.
• Remove "int" PK/FK columns.

Answer 4

• 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 5

This is relevent in a system that implements the distributed computing model. If the system is required to know the primary key at the time when you persist information in the system, the use of a auto-incrementing primary key maintained by ONE handler will slow down the system. Instead, you need a mechanism like a GUID generator to create primary key (keep in mind that the true feature of a primary key is its uniqueness). So, I can scale up with multiple services, each creating its primary key, independently of each other.

I had dubious privilege of doing this before and basically what I had to do was to export the whole damned database into XML. Next, I had a Java application that uses the java.util.Random's nextLong() function to replace the primary key with their new guid keys. After that I imported the whole thing back in to the database.

Of course, the first time I tried to import the XML files back, I forgot to turn off the auto-number feature of the primary key field, so do learn from my mistakes. I'm sure that there're better ways of doing it, but this was a fast and dirty way of doing it ... and it worked. In case you wondering, the project was to make the application scale.

Answer 6

Funny I think I need to do the exact opposite because of another problem...

http://stackoverflow.com/questions/138302/how-can-use-sqlbulkcopy-on-a-table-with-a-guid-primary-key-and-default-newseque

Answer 7

It's a very good choice. I switched from longs to UUID for one of my applications and I don't regret it. If you use MS SQL Server it is included in standard (I use postgresql and it's only included in standard from 8.3 on).

Like mentioned by Glenn Slaven, you can recreate UUIDs from the keys you have in your current records. Be aware that they will not be unique though but that way it's easy to keep the relationships intact. New records you create after the move will be unique.

Answer 8

DON'T DO IT! We started out using GUIDs, and now we've almost finished moving to INTs as PKs; we're retaining the GUID for logging purposes (and for some tables of, er, "negotiable relational integrity" ;) ), but the speed increase of using ints has been phenomenal.

This only really became apparent when the table rowcounts crossed into millions, mind you.

Our biggest folly by far was using a NEWID() as the PK of our (sequential) log table - there was much head-smacking when we realised our error.