varchar(1) VS char(1)
5February 15, 2016 by Kenneth Fisher
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:
[…] Kenneth Fisher warns against low VARCHAR sizes: […]
It is an old but always interesting question.
As I remembered that it was debated in several questions on the MSDN/TECHNET forums , I am preferring to give 2 links
https://social.technet.microsoft.com/Forums/en-US/a35a8d23-affd-455d-beea-ccd77f9ef785/choosing-between-charn-and-varcharn?forum=transactsql
and
https://social.technet.microsoft.com/Forums/en-US/home?searchTerm=difference%20varchar(1)%20char(1)&sort=relevancedesc&brandIgnore=true&page=1
Anyway the explanations given by Kenneth are easy to understand so cheers !!!
Thanks! I appreciate that. Easy to understand is actually one of my top goals 🙂
I made this comment on a more recent topic but thought it would be useful here, too…
VARCHAR(1) does behave slightly differently with LIKE comparisons.
VARCHAR(1) = ” is NOT LIKE ‘ ‘ while CHAR(1) = ” IS LIKE ‘ ‘
However, this is such a specific difference that I cannot believe anyone in their right mind is relying on it (and almost no-one knows about it unless they are disturbed enough to have tested it)
ANSI_PADDING is also going to effect how that works.