Char vs Varchar when not always populated

Question

I have a database with a field that holds permit numbers associated with requests. The permit numbers are 13 digits, but a permit may not be issued.

With that said, I currently have the field defined as a char(13) that allows NULLs. I have been asked to change it to varchar(13) because char's, if NULL, still use the full length.

Is this advisable? Other than space usage, are there any other advantages or disadvantages to this?

I know in an ideal relational system, the permit numbers would be stored in another related table to avoid the use of NULLs, but it is what it is.

Answer 1

The biggest advantage (in general, not necessarily your specific case) I know of is that in code, if you use varchar, you don't have to use a Trim function every time you want it displayed. I run into this a lot when taking FirstName fields and LastName fields and combining them into a FullName. It's just annoying and makes the code less readable.

Answer 2

If the field should always be exactly 13 characters, then I'd probably leave it as CHAR(13).

Also, an interesting note from BOL:

If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

Edit: How frequently would you expect the field to be NULL? If it will be populated 95% of the time, it's hardly worth it to make this change.

Answer 3

Well, if you don't have to use as much space, then you can fit more pages in memory. If you can do that, then your system will run faster. This may seem trivial, but I just recently tweaked the data types on a a table at a client that reduced the amount of reads by 25% and the CPU by about 20%.

As for which is easier to work with, the benefits David Stratton mentioned are noteworthy. I hate having to use trim functions in string building.

Answer 4

if your are using sql server 2008, you should look at Row Compression and perhaps sparse fields if the column is more ~60% nulls.

I would keep the datatype a char(13) if all of the populated fields use that amount.

Row Compression Information: http://msdn.microsoft.com/en-us/library/cc280449.aspx

Sparse columns: http://msdn.microsoft.com/en-us/library/cc280604.aspx