Another alternative is to use the embedded UUID in the jvm:
createObject("java", "java.util.UUID").randomUUID()
Also note that the UUID’s spit out by the java util is completely different than the ones from CF’s createUUID() function.
The ColdFusion UUID format is as follows:
xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx (8-4-4-16).
While java UUID’s are this format:
31bae32d-d7cc-41f3-a07a-504fb8c654c6
8-4-4-4-12
And the letters are in lower case.
I seem to also recall that CF UUIDs are more predictable than those
created by Java because CF uses the system clock to create them and
Java uses a random generator.
Jochem Van Dieten has some recent blog posts getting into Cf vs
SQLServer UUIDs and how they relate to storage requirement
requirements, especially in a Unicode space.
http://jochem.vandieten.net/tag/uuid/
In the past I've been a strong proponent of autoincremented numeric
primary keys but I'm working on the start of a system soon that will
require a lot of database replication and matching up of keys from
multiple systems, both of which are much easier to do with UUIDs. If
you are on SqlServer, you should look into UniqueIdentifier as a data
type. It reduces the size of storing a UUID significantly as it
converts it to a 128-bit integer. Neat stuff.
Judah
So would you recommend UUId as the dataype or varchar.
I usually have been just doing uuid as the value of my primary keys with varchar 26.
Any thoughts?
I'm going with uniqueidentifier for now. SQLServer 2005 has a new
function called NewSquentialID() that generates sequential UUIDs which
seems like a weird thing to me but that's how it does it. Here is an
article that discusses the squential uuids versus random uuids versus
bigint autoincrement ids.
It notes parenthetically a link to a concise point about the downside
to using a UUID as a primary key, which is that if you have a
clustered index, a new random UUID will insert the record somewhere
into the middle of the table instead of appending on the end. That
means rewriting a bunch of stuff internally and slowing down INSERT
operations significantly. This may or may not be a problem for your
application but as table sizes grow, inserts will get slower and
slower.
http://www.styledesign.biz/weblogs/macinnesm/archive/2004/09/14.aspx
I haven't decided on sequential uuids, random uuids or bigint with a
surrogate uuid key defined as a uniqueidentifier in the new app I'm
going to build. I need to find out more about replication strategies
for autoincremented bigints and see if the downside there makes up for
the storage issues with uuid pks and whether the sequential
predictability of NewSequentialID() is a big enough security concern
to outweigh the INSERT speed issue with a random NewID() created UUID.
Judah
Oh and do take a look at Jochem's blog posting on UUID and Unicode.
Basic rundown is that if you are going against a varchr(36) field and
using a Unicode enabled datasource in CF along with cfparam, then you
aren't really using a prepared statement because the text is coming
into the database as an nvarchar which means that it has to do a CAST
to Varchar and you can't do a prepared statement with the CAST in
there. Horribly inefficient having to do a CAST on all of your primary
keys.
I swear there was a cf-talk thread on this subject too but I can't
seem to find it right now.
Judah
And, of course, I find it right after I hit send:
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726
The bits about implicit conversion between nvarchar and varchar and
unicode are down toward the end.
Judah