I know a lot of developers like using UUID's for primary keys instead of integers. But one thing I see a lot is the use of the varchar datatype to store these strings.
If your using ColdFusion's CreateUUID() function to generate a unique identifier, you will notice that it always returns a 35 character string. So why store it in a variable length column in the database? Instead of using varchar (35) you can simply use char (35).
As a rule, if you are storing a fixed length string in a database use char rather than varchar. A varchar will have an extra byte of overhead on most databases (usually an integer stating the actual length of the string).
Some other common places where you should use char over a varchar include:
- Two Character US State Codes
- Two Character Country Codes
- One Character Status Flags
Update: Many databases now have native UUID data types, which can store the uuid as in binary, which is even more space efficient.
Comments
I know I'm way late on this conversation, but I've been researching this whole UUID thing today, and Pete is 100% correct on the CF's CreateUUID() creating a 35 character string. This is due to the fact that CF generates 4 segments instead of five, and therefore has one less hyphen. The actual UUID standard is 32 characters, but most have 36 when including the 4 dashes between their 5 segments. And these aren't the only options. The following shed light on Daniel's error: http://en.wikipedia.org/wiki/UUID http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_c-d_24.html That said, thanks Pete for sharing this. I first ran across using UUID looking at Ray C.'s apps recently.