The SQL CHAR data type is definitely a common data type you should know when querying and developing SQL Server databases.
In this very brief tutorial, I’ll teach you everything you need to know about the SQL CHAR data type, and why it is a great choice for storing character string data.
The CHAR data type is actually one of 4 character string data types available to us in Microsoft SQL Server. The others are NCHAR, VARCHAR, and NVARCHAR. I have a full tutorial on all these different data types for you to check out:
SQL Server character data types (and the differences between them)
I also have a great FREE guide you should get that discusses the most common data types you will likely encounter as a database professional. You should definitely understand these common data types if you want to perform your job well. Here’s the link:
FREE EBook on SQL Server Data Types!
Here’s what we’ll discuss in this tutorial:
- What is the CHAR data type?
- An example of using CHAR
- The disadvantage of using CHAR
- Tips about CHAR
Let’s start from the top:
1. What is the CHAR data type?
The CHAR data type is used to store character string values. It is ideal when the values you want to store will all be similar in size.Â
The syntax of CHAR is very simple. An example would be CHAR(20). This will store a character string value that is at most 20 characters long.
2. An example of using CHAR
Here is an example of using the CHAR data type when creating a table:
Here’s a sample of the data we can enter into this table:
Notice the EmployeeCode column. This column is meant to hold a unique code that represents the initials of an employee, plus a number. The number at the end is used in case two people have exactly the same initials, like in the case of Michael Booth and Monica Bennett.
This column uses a CHAR(3) data type. The use of CHAR in this case is ideal because all EmployeeCode values will likely be the same size of 3 characters. I suppose the only way someone would not have 3 characters is if they didn’t have a last name, like Prince or Cher (very unlikely!).
3. The disadvantage of using CHAR
To understand the disadvantage of CHAR, we need to understand how SQL Server will store CHAR values in memory.
SQL Server will reserve the full amount of memory defined by the data type when storing a value, even if the value doesn’t need that full amount.
In our table, notice the LastName column uses a data type of CHAR(20). Now think about the LastName ‘Gray‘, which is only 4 characters long. Even though this value only needs 4 bytes of storage (one byte per character), SQL Server is still going to reserve the full 20 bytes to store this value. Of those 20 bytes, only 4 of them will actually get used. The remaining 16 will be literally empty.
Does that sound like a huge waste of space? (Say yes)
The CHAR data type basically says “You said you needed 20 bytes, so you FREAKING GOT 20 BYTES. I don’t care how much of it is allegedly wasted“. Yikes!
Ergo the VARCHAR data type
I should have used the VARCHAR data type for my LastName column (and probably the FirstName column, too). VARCHAR will reserve only as much space as is needed to store the value, and nothing more. In other words, VARCHAR would have only used the necessary 4 bytes to store the name ‘Gray‘ and nothing more.
The VARCHAR data type basically says “I was prepared to give you 20 bytes, but I see you only need 4, so I’ll give you those 4 and give the rest back to the operating system“.
So again, if all your values are the same size, there really isn’t a disadvantage in using CHAR at all. If all your values are the same (or very similar) size, and you outline that size when defining the data type, then there’s no problem! It’s only a problem if your values will vary in size. In that case, you should use VARCHAR!
Learn more about VARCHAR:Â SQL VARCHAR Data Type: The Ultimate Guide for BeginnersÂ
4. Tips about CHAR
There is only one tip I can share about the CHAR data type:
Tip # 1: You can leave out the size specification and the size will default to 1.
Take a look at the following Products table. It has an ‘InStock‘ column to represent whether or not a product is in stock:
Notice we didn’t specify a size for our InStock column. This means the size defaults to 1. This is fine for this column because we only want to store one character anyway (either ‘Y‘ or ‘N‘).
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, don’t forget to download your FREE guide:
FREE EBook on SQL Server Data Types!
This guide discusses the most common data types you will likely encounter as a database professional. 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!
