When working with character string data types in Microsoft SQL Server, you might see CHAR and VARCHAR and wonder, “What’s the difference between the two“?
In this very brief tutorial, we’ll discuss the difference between CHAR and VARCHAR. I actually have a full tutorial on all the character string data types for you to review. The difference between CHAR and VARCHAR is also discussed in that tutorial. You can find it here:
SQL Server character data types (and the differences between them)
Let’s get right into it:
What’s the difference between CHAR and VARCHAR?
When storing a value in memory, CHAR reserves the entire amount of memory as defined by the data type, regardless of how much is actually needed. VARCHAR will reserve only as much space as is needed to store the value, and nothing more.
Let’s take a look at the following Employees table:
Notice it has a mix of CHAR and VARCHAR columns. We’ll learn why you should choose one type over the other, depending on the data you want to store.
Here’s the content of the table:
Let’s start with the FirstName column, which is data type VARCHAR(20). As mentioned earlier, VARCHAR will use only as much memory as is needed to store the value, and nothing more.
Think about the name ‘Liz‘ in that column. This is obviously only 3 characters long, meaning it requires 3 bytes of memory (1 byte per character). When SQL stores this value in memory, it will reserve only the 3 bytes required to store the value, and nothing more. Sure, we have a total of 20 bytes available to us as defined by the data type, but we don’t need that much.
Now let’s think about the LastName column, which is data type CHAR(20). As mentioned earlier, CHAR reserves the entire amount of memory as defined by the data type, regardless of how much is actually needed.
So think about the last name ‘Boyle‘. This is only 5 characters, which means it needs 5 bytes of storage….
But SQL Server will reserve 20 bytes to store this value.
SQL Server will reserve 20 bytes to store something that is only 5 bytes big. In other words, out of the 20 bytes, 5 bytes will be used and the remaining 15 will be empty.
Does that sound like a huge waste of space? (say yes)
I should have used VARCHAR as the data type for the LastName column, too. As you might have noticed, we should choose to use VARCHAR when the lengths of values we want to store will vary greatly. We should choose to use CHAR when the lengths of values we want to store will be similar in size.
To store the FirstName value of ‘Liz‘, the VARCHAR data type basically says “I was prepared to give you 20 bytes, but I see that you only need 3, so I’ll give you those 3 and give the remaining 17 back to the operating system“.
To store the LastName value of ‘Boyle‘, the CHAR data type basically says “You said you needed 20 bytes, so you FREAKING GOT 20 BYTES. I don’t care how much space is wasted“. Yikes!
The ‘State‘ column demonstrates a better use of CHAR. This column uses the CHAR(2) data type. This column is used to store the 2-letter abbreviation for the state that an employee lives in.
All 50 states in the United States use a 2-letter abbreviation. In other words, the length of characters we’re going to store in this column won’t vary greatly. In fact, it won’t vary at all. For each value in this column, SQL Server will reserve 2 bytes of storage, and we know that all 2 bytes will be fully populated, which is good!
The downside of VARCHAR
We’ve already talked about the downside of CHAR, in that it can potentially waste a lot of space if used inappropriately. But is there a downside to VARCHAR?
Yes, there is. Think about the name ‘Liz‘ that we’re storing. Again, right now this name is taking up only 3 bytes of memory. But what if we need to change the name to something bigger? For example, what if we decide to change the name to her full name, ‘Elizabeth‘?
Well, suddenly we need more space. SQL Server needs to find and allocate the space necessary to store the new, longer name of ‘Elizabeth’ which appears to be 9 bytes worth.
If we had used CHAR as the data type instead, we would have had that space allocated and available already. Remember, with CHAR, the whole 20 bytes would be allocated to store the value. It would be no trouble at all to go from using 3 bytes of that to using 9 bytes.
Understand that while this is technically a downside, it’s not that much of a downside. SQL Server is very quick at finding and allocating new memory. It’s a much less forgivable sin to waste space, like what might happen if you use CHAR inappropriately!
Next Steps:
Leave a comment if you found this tutorial helpful!
I hope you found this tutorial helpful. If so, I have a great FREE guide you should download:
FREE EBook on SQL Server Data Types!
This guide discusses the most common data types you will likely encounter as a database professional, including the character string data types. You should definitely understand these common data types if you want to perform your job well. Download the guide today!
Also, if you want to learn more about all the different character string data types, check out my full tutorial on the topic:
SQL Server character data types (and the differences between them)
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
