Should I better use varchar(6) or char(6) for an SQL Server column which values can be either 6 or 5 symbol long?

Question

I use ISO 3166-2 codes to designate states (of many countries, not just the USA) in my database. Code is the primary key of my States table and and it is referenced by StateCode foreign key column of my Cities table.

As far as I can see, there are 5 (like US-CA) and 6 (like RU-SPE) symbol long codes in the standard.

Should I better use varchar(6) or char(6) type for the columns in SQL Server?

The database servers to be used are going to be SQL Server 2012 and Azure.

The application to access the data are based on ASP.Net 4.5 MVC 4 and Entity Framework 5.

Answer 1

From MSDN (the page in question is for SQL Server 2000, but should still apply):

• Use char when the data values in a column are expected to be consistently close to the same size.
• Use varchar when the data values in a column are expected to vary considerably in size.

I believe the recommendation is based on varchar needing extra space to store the length of the string, and (possibly) having the string stored separately from the rest of the table, resulting in slower fetches.

Hope that helps!

Answer 2

CHAR(6) is always going to be 6 bytes for each entry - no matter what you enter.

VARCHAR(6) is going to be 7 bytes with 5 characters (5+2 overhead), 8 bytes with 6 characters.

Any string below roughly 10 characters, I usually pick the CHAR type over the VARCHAR because the variable-length VARCHAR type always has 2 bytes overhead for each entry.

So if you have almost exclusively 5 and 6 character strings in this situation, I would recommend using CHAR(6).

I would recommend VARCHAR if you have longer strings, and if the variation in the length of the actual stored text inside that string is larger; e.g. I would not use CHAR(256) for an e-mail address - in that case, use VARCHAR(256) since the lengths of the e-mail addresses stored in those columns will be significant, and the 2-byte overhead for the variable length string doesn't really matter all that much compared to 256 characters in the string.