Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

A Look at GUIDs Expand / Collapse
Author
Message
Posted Tuesday, July 11, 2006 10:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 12:38 PM
Points: 6,705, Visits: 1,682
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
Post #293555
Posted Sunday, July 23, 2006 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 25, 2007 11:52 AM
Points: 4, 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.
Post #296581
Posted Sunday, July 23, 2006 1:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, 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
Post #296582
Posted Sunday, July 23, 2006 10:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:33 PM
Points: 138, Visits: 45

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

Post #296616
Posted Monday, July 24, 2006 1:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 09, 2011 7:49 AM
Points: 343, 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!



Post #296634
Posted Monday, July 24, 2006 6:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 03, 2014 7:07 AM
Points: 3,627, Visits: 330
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


Post #296671
Posted Monday, July 24, 2006 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 09, 2011 7:49 AM
Points: 343, 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.


Post #296672
Posted Monday, July 24, 2006 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:52 AM
Points: 298, Visits: 332

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
Post #296679
Posted Monday, July 24, 2006 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 08, 2006 11:16 AM
Points: 1, 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. 
Post #296745
Posted Tuesday, July 25, 2006 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,077, Visits: 8,919

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

John

Post #297039
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse