SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Look at GUIDs


A Look at GUIDs

Author
Message
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25339 Visits: 2746
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/alookatguids.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Richard Yeo
Richard Yeo
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 1
If you are writing cross platform code then using GUID's could certainly be useful. Different RDBMS's implement Identity columns in different ways. Returning Indentity values has always been a bit of a pain. I can see advantages of using GUID's particularly if you don't like using stored procedures and keep your logic in the middle tier.
Jamie Thomson
Jamie Thomson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2695 Visits: 188

Not a bad article Andy. I learnt some stuff I didn't know before.

Having said that, the article was a "How" to use them. I would really likemto see a "Why" I should use them Specifically, why would i use them rather than generate sequential IDs (which is what I do currently - I don't use IDENTITY).

-Jamie



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Jonathan Schafer
Jonathan Schafer
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 46

I never use GUID's as primary keys. However, I have found them very useful in the following scenario. Because the generated numbers are essentially random and unique, whenever I need to randomly select a number of records from a group, I assign a GUID to each row. Assume a table with 1000 records and I want to select 100 at random. I can assign a GUID to each row, and then select the top 100 ordering by the GUID. Because the generated GUID's are random, I know that the rows that get selected will be sufficiently randomized. This has worked well for me in the past.

Jonathan


Stewart Joslyn
Stewart Joslyn
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 188
There always is the risk that GUIDs are only unique if the network cards generating them have unique ids. They're supposed to but not if they're clones!

The scenario of generating ids on the client without the need for round trips is addressed more easily by giving each client an unique integer id (I assume that you know who the clients are - not just anonymous access) and concatenate that with the client's internally generated unique integer ids for each object. That gives, at worst, 8 bytes instead of 16, provides an audit trail identifying the originating client and is human readable.

I see human readability as a major disadvantage of GUIDs - they make debugging and issue investigation a nightmare!



lionfan91
lionfan91
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5332 Visits: 431
GUIDs also add a level of security, as was noted in the article, which is especially important in government and military developed applications. I develop a number of security-related and classified web and client/server applications, and using a GUID as the primary key helps prevent users from seeing records that are not theirs. If we exposed an Identity PK that had values 1 and 4 for a specific user, it doesn't take a rocket scientist to figure out that there are probably records with IDs of 2 and 3 too. Depending on how securely written the app is, it may or may not be easy to get at those records. Very difficult to "guess" like this when using GUIDs. Despite the performance hits a GUID may come with, we find that the benefits of GUIDs from a security point are worth it. This logic probably has merits in other arenas too, especially e-commerce and financial-related systems. My 2 cents... Steve



Stewart Joslyn
Stewart Joslyn
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 188
I take your point but surely this is the very last line of defence. All keys should be internal to the app / db and invisible to the user who should not have any direct access to the tables anyway. All comms, of course, should be encrypted for virtually any system.



BobAtDBS
BobAtDBS
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 365

Excellent, factual article Andy.

I started using GUIDs as Primary Keys for the very reasons mentioned in your article several years ago (SQL 7 also has NEWID as a function). My criteria was

1) Being able to generate the PK from the client and passing it to associated recordsets long before uploading the batch to the server. This was really big for us, our clients are typically on a slow connection, a long ways from the server.

2) Designing the database ready for replication, where a GUID is going to get added to the table anyway (This really kills the 16 byte versus 4 byte argument, if you use replication you'll end up with both if you start with the Identity field).

3) Deciding that the performance issue with page splits was pretty bogus for our particular usage patterns. Few folks ever mention that using GUIDs for PKs solves another problem at the same time. If you use Identity as your PK and have a lot of inserts, you end up with one hot spot on the hard disk where all the activity is taking place, with everyone needing to update the same page. With GUIDs, the activity gets spread around a bit. In my experiments (six years ago), I got higher insert rates with GUIDs than with Identity values.

The purists (you know who you are) always trot out the "a good primary key should be a natural key" argument. I just don't care. It is really nice to be able to do every join on a 1 to 1 FK to PK basis. It is really nice to know that no user, no manager, no third party is EVER going to need to change a PK because somewhere, someone allowed a PK with a value that has human readable significance.




Student of SQL and Golf, Master of Neither
Eric-340515
Eric-340515
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1
One reason not to use integer PKs and instead use GUIDs is if the table is potentially going to exceed 4 billion records (for instance, the history table to your primary transaction table). A 4 byte integer holds values between -2,147,483,648 to +2,147,483,647, so you are in trouble if any table exceeds 4.2 billion records.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34506 Visits: 16651

Not if you use bigint, which is 8 bytes. Then you can have more than 18 billion billion records in your table.

John


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search