Yep, SQL Server's GUID is a 16 byte type. Remember, GUIDs are *four times longer* than integer keys and thus require more overhead than integers for all operations. This includes searching and sorting. In large DBs (which I work with regularly), this can very quickly become a huge performance bottleneck. With the integer data type capping out at upwards of 2 billion, there are very few instances where a larger type is necessary, and in such situations, even bigints (at 8 bytes and a ceiling of 9,223,372,036,854,775,807 - 9 quintillion in the American number system, 9 trillion in the UK/Euro system) will outperform GUIDs. The _only_ time I would ever use a GUID is for replication, and even then, I do not use them as my primary keys. There are other ways of performing replication with them as secondary keys.
Roland -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim Davis Sent: Wednesday, December 08, 2004 5:43 PM To: [EMAIL PROTECTED] Subject: RE: [CFCDev] GUIDs as Primary/Foreign Keys > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Adam Cameron > Sent: Wednesday, December 08, 2004 3:18 PM > To: [EMAIL PROTECTED] > Subject: RE: [CFCDev] GUIDs as Primary/Foreign Keys > > >I haven't run into any performance problems yet. Presumably, with a > large enough dataset you might, but SQL Serve handles indexed strings > nicely enough in most cases. > > Are UUIDs stored as 36-char strings or 128-bit integers? In SQL Server you can create a column as datatype "uniqueindentifier" - if you do you must pass the ID into SQL in the format it expects (not CFs format - but it's easy to convert) or use the NewID() function. If you do that I believe the data is stored internally as a 128 bit number (the column has a length of "16"). If you store it yourself or want to use a different format you have to create a char column of length 36 (or more I suppose). Jim Davis ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at [EMAIL PROTECTED] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at [EMAIL PROTECTED]
