varchar(1) VS char(1)

5

February 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

Varchar1

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:

5 thoughts on “varchar(1) VS char(1)

  1. […] Kenneth Fisher warns against low VARCHAR sizes: […]

  2. papynormand says:

    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 !!!

  3. Simon Holzman says:

    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)

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 6,758 other subscribers

Follow me on Twitter

Archives

ToadWorld Pro of the Month November 2013