Pete Freitag Pete Freitag

Use Char instead of Varchar to Store UUID's

Updated on August 16, 2022
By Pete Freitag
databases

I know a lot of developers like using UUID's for primary keys instead of integers. But one thing I see alot 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.



varchar char sql databases createuuid uuid

Use Char instead of Varchar to Store UUID's was first published on February 16, 2007.

If you like reading about varchar, char, sql, databases, createuuid, or uuid then you might also like:

Discuss / Follow me on Twitter ↯

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.
by Kevin Parker on 08/27/2009 at 7:33:52 PM UTC