Varchar Vs Char

Every once in a while I’ll find a field someone created where the datatype choice was not the best. This comes up relatively often with Varchar vs Char. All of the confusion just comes from not understanding the differences and causes and effects of each. With that, I’ll outline the only times I will use one over the other.

Let’s take a look at the behavior of char vs varchar:

[cc lang=”sql”]
CREATE TABLE #meme
(
first_name char(50),
last_name varchar(50)
)

INSERT INTO #meme
(first_name, last_name)
SELECT ‘john’, ‘smith’

SELECT * FROM #meme

SELECT DATALENGTH(first_name), DATALENGTH(last_name)
FROM #meme
[/cc]

You can see from the value returned from DATALENGTH that first_name is 50 bytes long despite only taking 4 characters. The other characters are empty strings.

Storage wise, varchar is out the gate up to 2 bytes larger than a char because it needs to store the length. It’s 1 byte larger if it’s over 255 characters. That’s why you will often see varchar definitions as varchar(255). Pain-in-the-butt wise, char is far more a pain to deal with unless all the data in the char column takes up the exact amount of space defined for the char column. I typically only use char if all the fields are going to be the exact same size guaranteed. Even then, I’m hesitant. Partly because disk space is not as much of an issue as it was before, and mostly because if you DO have variable length fields in a char column, then the remaining spaces you don’t fill up, will be padded with empty spaces. This forces you to use the LTRIM(RTRIM()) debacle for every row you return.

There are potential situations where you would clearly want to use char over varchar. Suppose you had an initial value with a small data length that was going to be updated with a large data length. In other words, if you defined a varchar(255) that initially got populated with the word “Hi”. And over time, this field continually got updated and the size of it continued to grow to fill the whole 255 max defined size. In this situation you would have bad fragmentation because as the size of the field grow, if there was not enough room on the data page another page would have to be created to house the new data.

Also, if just for storage purposes and no retrieval, you may want to consider char if space will be a factor for you. As you can see, there is no cut and dry answer for your decision to use char over varchar, but overall if you have a good maintenance plan that deals with fragmentation and you plan to retrieve the data often, and the data is variable length that doesn’t vary widely, varchar is probably the better choice.