Everyday Problem Solvers
Why integers are lousy primary keys
July 30, 2012Posted by on
When I was a noob (about 2 years ago) I took a freelance gig to develop a simple system for a small shop. Very basic stuff. Few weeks ago the owner called me saying that he was very happy but he was facing a problem… You see, his business grew and he now has 3 shops, all of them using my system, and he wants to consolidate on one database. Well, as you can imagine I only managed to accomplish this by replacing the primary keys. They were all integers, now they’re GUIDs. The reason? Integers clash very easily!
GUIDs when generated (and not pulled out of your head) are by definition unique, and so, by logical deduction do not clash, and by another deduction when applied to primary key column, it will not only guarantee the uniqueness of that key in the table, but in the entire universe!
Something that you do have to be carefull about is not to put a clustered on it! You see cluster index define physical order of records and the very nature of a GUID is that it’s random. If you do rest assured that inserts will have a very poor performance.
You must be wondering what I did to successfully operate the transition. Well here it is a basic roadmap:
- Foreach integer column that is used as a primary key create a GUID column
- run this: Update myTable set myNewGUIDColumn=newid()
- on the tables that there’s a FK repeat nº1
- run this: Update myOtherTable t1 set myNewFKColumn = (Select myNewGuidCOlumn from myTable t2 where t1.myOriginalPK=t2.myOriginalFK)
That’s it! And of course adapt your code to handle GUIDs instead of integers…