A lot of web applications these days use a UUID as the primary key instead of the int/identity combination (speaking for MSSQL that is).
Now there is a (valid) argument that UUID’s aren’t great for primary keys due to the fact that primary keys are clustered, meaning that integers would work much better if the table had a large amount of data. This may not effect you dramatically as you’d would most likely only see performance degradation once the database had a substantial amount of data.
Anyway, creating a UUID is often handled by the createUUID() function in ColdFusion which produces a 35 character string in the format of:
xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx (8-4-4-16).
However you can also generate a unique id at the database level which is a slightly different format to ColdFusions UUID. The Microsoft/DCE standard uses the following:
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (8-4-4-4-12)
Lots of developers prefer to let the database handle the creation of primary keys instead of the application (ColdFusion). Having the database create this can be done using the MSSQL newID() built in function. Note that this will produce a 36 character string, 1 more than ColdFusions createUUID(). You have the option to use something like char(36) or varchar(36) as the column datatype, but you could also use the uniqueidentifier datatype.
This can cause a few incompatibility problems if you are expecting the ColdFusion standard in your code. So to maintain consistency and possibly prevent database/code errors you can simply convert the database level newID() to the ColdFusion UUID by removing the last (4th) hyphen in the SQL.
select left(newid(), 23) + right(newid(),12) as objectidor in the middle of a query:
INSERT INTO myTable (ID, FirstName)
VALUES (left(newid(), 23) + right(newid(),12), "Michael")In the situation where you might want to convert a ColdFusion created UUID to conform to the Microsoft standard, you can use the CreateGUID() function (by Nathan Dintenfass) from cflib as follows:
function CreateGUID() {
return insert("-", CreateUUID(), 23);
}UUID’s for database keys can be very handy but just watch out how and where you are creating them and how they fit into your application. Remember that if you did an
IsValid("uuid", myNewID)
where myNewID was created by newID() in the database…your test would fail!
I’ve always been puzzled by the comment that integers work better than UUIDs for indexes.
What is a UUID other than 128-bit integer?
*Surely* it is not actually stored in the DB as a string?
–
Adam
Adam Cameron
January 30th, 2007
FYI:
isValid(”guid”, myNewID)
Wouldn’t fail
David Stockton
January 30th, 2007
Just seen your comment Adam,
it depends on the datatype in the DB. That’s why it’s better to use the datatype “uniqueidentifier” rather than varchar.
D
David Stockton
January 30th, 2007
Hi Adam,
I guess that argument might come come across in several ways:
It’s more work during PK generation. If you want ColdFusion UUIDs then you either need to create it in the application (a no no for a lot of people) or use the technique above to convert a database GUID to UUID. This is of course fine…but a little more work than a simple int/identity.
Also you can’t do a SELECT @@identity, so if you are using the modified newID() to generate your PK this won’t work. You’d need to generate it before the INSERT (in the application or in a stored procedure etc) to be able to access it directly after an INSERT.
> *Surely* it is not actually stored in the DB as a string?
That would depend on the data type chosen for the PK field. If you’re using char or varchar then it would be a string and perform worse than an int when it came down to scanning indexes etc
If the data type was a uniqueidentifier it’s stored as a 16-byte binary value vs 4-bytes for an int.
A possible development annoyance is that you can’t use aggregate functions on UUID fields like min(), max() or even > and < (greater than and less than).
GUID/UUID are not the best for ORDER BY and GROUP BY queries
I suppose another reason is that ints are a lot easier to read!
Michael Sharman
January 30th, 2007
you can perform > and
tony petruzzi
January 30th, 2007
I’m an avid believer in UUIDs, but i can’t stand the hyphons. So much so that i strip the hyphons out completely. The problem is that Javascript doesn’t like hyphons in variable name. So in the fairly common situation where i have loop over PKs and dynamically name a hidden variable or a checkbox etc, the hyphons screw up the javascript code. Sorry hyphons, but you ahve to go:
Steve Nelson
January 30th, 2007
ugggh, < didnt work.
<cfset myid=replace(createuuid(),”-”,”",”all”)>
Steve Nelson
January 30th, 2007
Great post Michael,
Your concat function which uses newid() twice is actually creating two ms uuids and you’re combining them together. This is ideal for a user defined function which would be reusable across the database. I whipped up quick function for this and put it on my blog if you’re interested.
MSSQL CFUUID Function
Christopher Wigginton
January 30th, 2007
Tony: Although you can use > and < your primary keys are sequential so (particularly during development) you need to find something else to use when you want to quickly grab a range of records. Most likely you’ll have a DateTimeCreated or some such column, but an int/identity does make it easier
Christopher: Thanks for the link, nice one! If you were in a stored procedure you could of course create a single newID() and put it in a variable to use, but this way is just simpler! Thanks again
Michael Sharman
January 30th, 2007
Hi again
Sorry, I was not suggesting using a CF-formatted UUID for anything. I was meaning “an actual UUID” (not one of CF’s strings). So, on MS-SQL, yeah, a “uniqueidentifier”.
Why on Earth would you WANT to do max(), min(), on a UUID? It makes no sense to want to. I think if one finds oneself doing that sort of thing… time to rethink the DB schema, or what one’s actually trying to achieve (because the approach is probably wrong).
Anyway, interesting input from everyone, so that’s all good!
–
Adam
Adam Cameron
January 31st, 2007
“Why on Earth would you WANT to do max(), min(), on a UUID?”
should read
“Why on Earth would you WANT to do max(), min(), < or > on a UUID?
How come your blog removes angle-brackets (well, in this case, contextually they WEREN’T angle brackets).
–
Adam
Adam Cameron
January 31st, 2007
Hi Adam,
You might want to do aggregate functions on an int (identity), especially during development. You know…give me all users with an ID > 100 or something. Just makes is easy. You wouldn’t of course want to do it on a UUID
Sorry about the less than/greater than…I’m using Wordpress which is PHP. It’s actually fantastic, but I’d much rather be using a ColdFusion blog!
Something I need to look into soon
Michael Sharman
January 31st, 2007
I don’t like using them as a PK, but I do use them as a public ID for unsubscribe emails, URLs made public etc.
So if I need them I have both a regular userID column and a publicUserID column in the same table.
It’s worked out nicely so far.
Adrian Lynch
December 18th, 2007
[...] mightn’t be so bad. Another constraint is that it assumes an integer primary key which you may not be using, meaning the randRange() won’t work. Also what happens if you don’t have sequential [...]
chapter31 » Blog Archive » Returning random results with MySQL
March 28th, 2008