July 21, 2006 at 8:32 am
All,
I need some expert advice for you all. My company develop some products running on SQL2005. All our tables have surrogate key (Identity column) as Primary Key. Now the problem is two of our clients merged together both using our same product. Now we have to merge both the databases , there are hundreds of foreign Keys involved here using the Identity column. It was an expensive process to update all the Identity columns and the foreign keys values with new number (like multiply the Identity value (PK & FK) by 100000).
Now the issue is to avoid situations like this in future , our data architect suggest us to change our schema to using GUID as primary key instead of Identity column. There are lots of triggers which updates lots of FK to child tables joining the PK(identity column). Since it is not advisable to create cluster index on GUID , I have to look for an Alternate key for creating Cluster index on the table. My question is , I need to explain the team not to use GUID , can you guys please help me by listing the Pros and Cons for uinsg GUID as Primary key instead of Identity.
Thanks
July 21, 2006 at 10:40 am
In my opinion the biggest problem with using a GUID as a an active key is performance. If you're using an INT, that's a 4byte column. A GUID is 16bytes. That join process will be slower, not just measureably but visibly. Any indexes on those keys are also larger, taking longer to traverse.
Yes, the GUID will be unique even when you have to merge databases from separate systems, and that would eliminate the problem. But is that the business driver for this discussion? How many times will you need to consolidate two databases? It seems to me the likelihood of that occurring frequently would probably be low.
If you can setup a sample system with the switch to a GUID for a key to be used in joins and such, then compare the performance difference, I think the cons definitely outweigh the pros for the use of GUIDs.
Rather than change the fundamental data design, I would recommend coming up with a standard process for dealing with database consolidations in the future. But, that's just me...
Hope that helps.
July 24, 2006 at 9:16 am
I agree with James. You're proposing that every client suffer a performance hit every day, for ever, just because one merger was a pain. The extra twelve bytes doesn't sound like much, but it increases the size of every row, which requires more I/O to process, and is much less efficient in joins than an int.
How many such mergers do you expect to do?
Do you expect a merge like that to be easy? I would expect a significant amount of analysis would go into that process, GUIDs or not.
Now that you've done one merge, you'll be better prepared the next time.
We hired a new manager for web development who came in and mandated GUID primary keys for all web development databases. I didn't like it, but after all he was an expert at Web Development (imagine that in a Gothic font, accompanied by trumpets and huzzahs) so it became law. The web developers soon tired of having to constantly rewrite and fine-tune their stored procs and web pages to try to avoid timeout errors, so a mob with torches and pitchforks finally had this law repealed. He is still sometimes heard muttering "What's so bad about using cursors?", but we try not to pay attention.
There are problems that GUIDs are the solution for, but they come with a significant price tag. Maybe future hardware will effortlessly handle megabyte-size data pages, and 128-bit processors will handle GUIDs as easily as ints, but we're not there yet.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply