SQL Server – Designing Database with Only Text Fields

database-designdatatypessql server

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, dates, addresses, monetary values etc.).

This is not how I learned to build a database. When asked collegues said that it is easier this way. Is it bad practice to keep all fields in varchar? How could I argue that it should be changed?

Best Answer

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value.

There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient processing, or legible code.

Automatic validation is another foregone benefit with the all-text approach. You may think that a column contains dates but there will be nothing to stop someone entering the value '2014-13-97'.

Sorting is unlikely to give the intended result with columns which are "really" numbers. For example, if a column contained integers '1' through '100', and the query sorted by this column, one would expect the result to be 

1, 2 .. 10, 11 .. 20, 21 .. 100

However, the actual result is more likely to be

1, 10, 100, 11, 12, 2, 20, 21 ...

There may be similar concerns for dates, depending on the chosen character representation. Again type casting can cure this with the costs mentioned previously.

Occasionally you will find strings which contain digits only. Examples are national identity numbers, bank account numbers, phone numbers and such like. The common characteristic of such values is that it does not make sense to perform calculations on them. Other things being equal it may be OK to store these as char() columns, especially if they have optional embedded alpha characters, but the above considerations still apply.

Related Question