http://cflib.org/udf.cfm/createGUID
IIRC, using the UNIQUEIDENTIFIER datatype with GUIDs in SQL Server does have performance benefits over using a char(35) field with UUIDs. There used to be a bug in CFPARAM that made it so the GUID datatype would not validate unless it was all upper-case (and GUIDs came out of the DB as lower case), but I think that was fixed.
UUIDs definitely take up more storage per row and aren't as fast as what you can get from integer primary keys, but unless you have millions of rows (with storage being a concern) or are building a database where raw performance in the preeminent concern, I can strongly recommend use of UUID (absent those conditions, I doubt you'll see enough of a difference to warrant using integers for those reasons). A few highlights:
1) Code portability -- if you build your application to use GUIDs that you produce, it will run on basically any RDBMS, without needing to think about various different ways of having auto-incrementing integers
2) Data portability -- as Dave points out, moving data from DB to DB is very easy if you aren't relying on IDENTITY fields
3) Application coherence -- by controlling creation of IDs you never have the ugly code of needing to select out the ID you just created, which means fewer moving pieces to debug and fewer chances you forget a critical transaction in some part of your code -- for me, it also just feels "cleaner" to control my IDs from the application [NOTE: this assumes that your application is the sole/primary user of the database -- if that's not the case, this could end up being a slight disadvantage of not having the RDBMS own ID creation].
Dawson, Michael wrote:
As long as you don't mix the two types of GUIDs in the same column, it's not an issue. Even if you do, it won't make a difference unless you specifically use "uniqueidentifier" as the data type. Otherwise, use char or varchar.----------------------------------------------------------
I will say that I don't know if uniqueidentifier has better performance than a char that holds the same data.
Yes, the joins will be slower, but it will all depend on your implementation, number of records and indices. I don't think you will notice a difference until you get into the hundreds of thousands of records, if you have capable hardware.
MAD
------------------------------------------------------------------------ *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *Schreck, Tom *Sent:* Wednesday, December 08, 2004 8:27 AM *To:* [EMAIL PROTECTED] *Subject:* RE: [CFCDev] GUIDs as Primary/Foreign Keys
I had not thought of the ability to generate them before the db insert. That’s interesting. I do know that a CF GUID (createUUID()) is not the same as a SQL Server generated GUID (newID()).
What are your thoughts on performance of joins in select statements when you join on a GUID? It seems the database performance will decrease when querying using joins on GUIDs.
Thanks
Tom Schreck
972-361-9943
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]
