If you think you might ever want to do any flavor of replication, go with GUIDs. If you think you might ever want to model objects in your relational database, go with GUIDs (you might want to have IDs from the Person class and the Employee class mixed together at some point). In fact, just go with GUIDs. It is not any harder and you open up some more flexibility for yourself down the road for interop with other systems.
That said, the SQL server uniqueidentifier type is 4x larger than the int type, so in large (ie millions of rows) tables with lots of foreign keys, that will pile up and result in more pages and presumably contribute to slower retrieval times. BTW, there is another function at CFLib (MSSQLCreateUUID http://www.cflib.org/udf.cfm?ID=512 ) that allows you to create and select the format of your SQL Server GUID (binary or string). Binary representation doesn't need to be quoted in your query: Select * from myTable where ID=0x6F9619FF8B86D011B42D00C04FC964FF. This can sometimes help if you want to have a switch that substitutes a NULL for the GUID variable. If you have: Select * from myTable where ID = #ID# Then you can set #ID# equal to NULL or MSSQLCreateUUID('binary') and get away with it. You couldn't get away with that substitution if you used the string representation of the GUID- you would get an 'incorrect syntax' error. And no, the binary representation of a MSSQL uniqueidentifier doesn't just remove the dashes... FF19966F-868B-11D0-B42D-00C04FC964FF = 0x6F9619FF8B86D011B42D00C04FC964FF If you are working with Oracle, be sure to check out GuidToString http://www.cflib.org/udf.cfm?ID=705 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Dave Watts Sent: 08 December 2004 10:42 To: [EMAIL PROTECTED] Subject: RE: [CFCDev] GUIDs as Primary/Foreign Keys > from what you know/read have you seen any performance notes > on using GUID's vs. Identity int's? 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. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 ---------------------------------------------------------- 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]
