varchar(1) VS char(1)

3

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:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

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

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: