Use Char instead of Varchar to Store UUID's
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
As a rule, if you are storing a fixed length string in a database use
char rather than
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
- 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.
Like this? Follow me ↯Tweet Follow @pfreitag
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:
- Creating a ColdFusion UUID in MySQL
- Use varchar(max) instead of text in SQL Server
- Cheat Sheet for SQL Server
- Sphinx - Open Source SQL Full Text Search Engine
- Updated SQL Reserved Words Checker
- MySQL FULLTEXT Indexing and Searching
- SQL to Select a random row from a database table
- Insert Delayed with MySQL
That said, thanks Pete for sharing this. I first ran across using UUID looking at Ray C.'s apps recently.