The other day I made a somewhat flip coment on twitter. (I know, everyone is shocked right?)
varchar(1) Really? It’s going to be one of those kinds of days.
It started a rather amusing conversation over using varchar(1) vs char(1) and I thought it might be helpful to go over a few differences between the two. And my strong opinion that you should never use varchar(1) (or 2, or 3, or really anything under 10).
Let’s start with some code from my friend Jeff Rush (t).
CREATE TABLE #Test
(col1 char(1), col2 varchar(1))
INSERT INTO #Test
VALUES ('',''), (' ',' ')
SELECT
'''' + col1 + '''' AS char
,datalength(col1)
,'''' + col2 + '''' as varchar
,datalength(col2)
FROM #Test
The first thing you’ll notice is that a single space is stored the same way in both columns. With an empty string, on the other hand, we see a difference. Char columns are fixed length. So even though we inserted an empty string into it we get back a single space.
The next major difference is that varchar columns require an extra two bytes of storage. So a varchar(1) column actually uses three bytes not just the one byte that char(1) does.
That second one is the kicker for most people. Unless you REALLY REALLY need that fixed width it is in no way worth the loss of two bytes to use the varchar datatype. A single varchar column over a mil rows will cost you almost 2mb. It may not seem like much but it adds up. Quickly.
Additional reading:
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: data types, microsoft sql server, T-SQL 
