It’s very important to know a thing or two about data types in Microsoft SQL Server. We need to understand the differences between various data types so our tables don’t waste space, and so that our queries perform as efficiently as possible. One such data type we should understand is the VARCHAR data type.
In this very brief tutorial, we’ll go over everything you need to know about the SQL VARCHAR data type, and why it is a great choice for storing character string data.
The SQL VARCHAR data type is actually one of 4 character string data types available to us in Microsoft SQL Server. The others are CHAR, NCHAR, 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 Ebook 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. Get it today!:
FREE EBook on SQL Server Data Types!
Without further ado, here’s what we’ll discuss in this tutorial:
- What is the VARCHAR data type?
- An example of using VARCHAR
- The advantage of using VARCHAR
- The disadvantage of using VARCHAR
- Tips and tricks
Let’s start from the top:
1. What is the VARCHAR data type?
The VARCHAR data type is used to store character string data. We use this data type when the size of the values we want to store will vary greatly.
We’ll learn how VARCHAR is great for storing character string data in an efficient way.
The syntax for VARCHAR is very simple. An example would be VARCHAR(20). This will store a character string value with a maximum size of 20 characters.
2. An example of using VARCHAR
Let’s take a look at the following table:
CREATE TABLE Employees ( EmplID INT IDENTITY(50,10), FirstName VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20), EmployeeCode CHAR(4) )
Notice our FirstName, MiddleName, and LastName columns all use the VARCHAR(20) data type. This means each column can hold a string value that is at most 20 characters long.
Let’s insert some rows into the table:
INSERT INTO Employees (FirstName, MiddleName, LastName, EmployeeCode)
VALUES
('Michael','Douglas','Booth', 'MDB1'),
('George','Alexander','Gray', 'GAG1'),
('Rachel','Lynn','Washington', 'RLW1'),
('Liz','Denise','Boyle', 'EDB1')
3. The advantage of using VARCHAR
It’s easy enough to use the VARCHAR data type when creating a table, but what is the advantage of using this data type?
The VARCHAR data type will reserve only as much space in memory as is needed to store the value, and nothing more.
For example, think about the FirstName value of ‘Rachel‘, which is 6 characters long. We know the FirstName column can hold 20 characters, but we only need 6 for this particular value. So in memory, this value will use 6 bytes of memory (one byte per character), and nothing more. The remaining 14 bytes are given back to the operating system.
Compare this to the regular CHAR data type. CHAR will reserve the full amount of memory defined by the data type. If we had used CHAR(20) for the FirstName column, the name ‘Rachel‘ would reserve the full 20 bytes of memory to store this value. It doesn’t matter that we only actually use 6 bytes out of that. Essentially, those 14 extra bytes are empty!
This is what we mean when we say “VARCHAR will reserve only as much space in memory as is needed to store the value, and nothing more“.
The use of CHAR would be like building a 20 car garage to store your 6 vehicles. Why would you waste that much space? Wouldn’t it be cheaper and more efficient to just build only as much garage as you need?
4. The disadvantage of using VARCHAR
Let’s stick with that garage analogy. Let’s say you were wise and only built a 6 car garage for your 6 cars.
What if you decided to buy a seventh car? Where will you put it? Well, you’ll have to build an addition to your garage. I suppose if you had built a 20 car garage from the start, you would’ve been ok.
This is the disadvantage of using VARCHAR. If your value changes to a larger size, SQL will need to find and allocate the required space to store the new, larger value.
Take the FirstName ‘Liz‘Â from our example. Obviously this will only use 3 bytes of memory since we use the VARCHAR data type. But what if we decide to change this to the person’s full name of ‘Elizabeth‘? Well, now suddenly we need 9 bytes. SQL will need to find and reserve the extra space required to store all 9 bytes.
Again, compare that to if we had used CHAR(20) as our data type instead. In that case, SQL would not have needed to “find and reserve” anything. We would already have the full 20 bytes reserved! The name ‘Elizabeth’ will use 9 bytes out of that and leave the remaining 11 bytes empty.
Sure, we’re still wasting 11 bytes of space, but the point is we didn’t make SQL Server find and allocated more space to store our new value, which saved time!
5. Tips and tricks
Here is a list of a few things you should know about VARCHAR:
Tip # 1: You should use VARCHAR when the values you want to store will vary greatly, and use CHAR when the values will be similar in size.
VARCHAR is the ideal choice when you want to store values of varying sizes because each value will use the minimum amount of memory needed to store the value. But what if you know all of your values are going to be the same size?
Take a look at the EmployeeCode column of our table. The value in this column represents the initials of the employee, plus a number.
For example, the employee Michael Douglas Booth has an EmployeeCode value of ‘MDB1‘.
(We use a simple number at the end in case two people have exactly the same initials. In that case, we can give the second person a different number from the first person to make them unique)
Most employees will likely have an EmployeeCode that is four characters long. That’s why the column is CHAR(4). We don’t have to worry about wasting space because most people will use all four bytes!
I suppose we still could have used VARCHAR(4) as the data type for this column without any consequence.
Tip # 2: The default size for VARCHAR is 1
If you wanted, you could leave off the size specification and the size will default to 1. I suppose this is good to know if you want to make a column that stores a single character value, such as an ‘InStock‘ column that stores only ‘Y‘ or ‘N‘. Like this:
CREATE TABLE Products ( ProdID INT, ProdName VARCHAR(20), InStock VARCHAR --Stores only a single character )
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 encounter as a database professional, including the VARCHAR data type. 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:
Do you know the difference between VARCHAR and NVARCHAR? Find out!
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!
