Varchars kill the integers

Iknow several professional applications that use varchar fields to store typically invoice and order numbers, just because it makes it easier to fill this field with non-numerical characters (for example when issuing credit notes and that).

This is pretty irritating on the part of indexing and sorting results: after all, which comes first when using alphanumerical sorting: ‘10001’ or ‘200-1’? Most likely, you’ll lose a lot of speed too: sorting (and comparing) integers goes a lot faster than comparing sets of characters.

Instead of declaring these types of fields varchars, consider making them integer fields: after all, adding dashes (and other non-numerical characters) can be easily made part of a formatting mask in the UI of the application: heck, most graphical UI have masked edit field controls (Win 32/.Net/KDE/Gnome).

Additionally, on typical transactional databases like Postgres and Oracle, (integer) sequences are created atomically [transaction independent], which can save you a lot more headaches than trying to generate a unique combination of characters in a multi-user environment.

This entry was posted in SQL. Bookmark the permalink.

2 Responses to Varchars kill the integers

  1. NiElS says:

    Integers in varchars? You’re lucky. You should know how often I run into databases where everything is a “text” field. “But it holds text! Indexing? What’s that?”. Meanwhile these same people produce quite reasonable code. I guess the IT bubble drew a lot of trick-monkeys into the field.

  2. Arthur says:

    I run into databases where everything is a “text” field.

    Ugh. That sounds rather ugly.

Comments are closed.