How many characters allowed on VARCHAR(n) columns in SQL Server? How much memory VARCHAR variable takes in disk?

One of the frequently asked SQL questions in any programming interview is what is the difference between VARCHAR and CHAR data type in SQL? particularly when your project is using Microsoft SQL Server. You might have seen this couple of times, but given its popularity, nowadays people are asking this question differently e.g. they will ask you how much space a column of VARCHAR(2) data type will take? How many characters can it take? How do you minimize space? Is it better to use a CHAR variable instead of VARCHAR(2) and why? These are some of the really interesting questions and more meaningful than classical SQL questions like the difference between char vs varchar data type questions.

First of all, it will test the practical knowledge of candidates rather than theoretical knowledge in the case of earlier questions. Second, it has lots of useful follow-up questions to further check the depth of the candidate's understanding of SQL and database fundamentals. Let's try to answer these questions in this article.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through one of these SQL Server online courses. It's a great course to start with T-SQL and SQL queries in SQL Server.

How much space will a VARCHAR(n) column take in SQL Server?

One of the first questions on this topic is how much space a varchar(2) or varchar(3) column or variable will take in Microsoft SQL Server? Before answering this question you should clarify what the interviewer means by space here, does he mean space or memory used to store those values in the disk in terms of bytes.


If yes, then you can say that it's not constant, it depends upon the value you store. At the minimum, it will take 2 bytes to store varchar(2) value if nothing is stored on it i.e. empty String, and the maximum of 2+2 = 4 bytes if you store 2 characters on it.

For example, if you have a column called "Enabled VARCHAR(1)" and store values like "Y" or "N" then it will take 3 bytes in the disk, one for data, and 2 for metadata.

How many characters can be stored in a VARCHAR(n) column or variable?

The second important question related to VARCHAR variables and columns is about how many characters you can store on them and what will happen if you store more characters?

Sometimes, this is also asked as to what is the limit or capacity of the VARCHAR(2) column? How many characters can you store there?

The answer is simple but tricky for many programmers. In VARCHAR(2) variable or column, you can store String with a maximum of two characters e.g. empty String, one character string, or two-character String can go there.

Many programmers get confused on this point because of VARCHAR, they think that because it's varchar, you can store as many characters you want, but that is not true. The variable part comes in the actual memory required to store the value into disk e.g. less memory is required to store less character and more memory is required to store more characters. You can also join these best SQL and Database online courses to learn more about this concept.

Should you use the CHAR data type here?

It's advised to use the CHAR or fixed-length data type for small data because of the increased overhead required with VARCHAR. Since CHAR doesn't require extra 2 bytes required by VARCHAR to store metadata, it's better to use CHAR(2) instead of VARCHAR(2) because if you store two characters CHAR(2) will take 2 bytes but VARCHAR(2) will take four bytes.

This small saving in choosing the right data type can be significant for large tables with millions of records. You can also join these free SQL Server courses to learn more about the difference between char and varchar data types in SQL.

That's all about how much memory VARCHAR(2) columns take and how many characters you can store in a VARCHAR(2) column or variable in SQL Server. Even though it's quite fundamental and every Microsoft SQL Server developer should know that you would be surprised with many Java and a .NET developer working with SQL Server doesn't know most of the details. If you like to add any follow-up questions on this concept feel free to add, that will help all of us.


Other SQL Server articles you may like

• Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
• How to replace NULL with empty String in SQL Server? (tutorial)
• How to remove duplicate rows from a table in SQL? (solution)
• How to split String in SQL Server 2008? (answer)
• How to join more than two tables in one SQL query? (solution)
• 5 tips while migrating from Oracle to SQL Server? (tips)
• How to find the second highest salary of an employee in SQL Server? (query)
• What is the difference between WHERE and HAVING clause in SQL Server? (answer)
• How to find duplicate records from a table? (solution)
• 5 Web sites to learn SQL online for FREE? (resource)
• How to find all customers who have never ordered? (solution)
• What is the difference between close and deallocate a cursor? (answer)
• How to create an Identity column in SQL Server? (example)

Thanks for reading this article, if you like this article then please share it with your friends and colleagues, it makes a lot of difference. If you have any suggestions, feedback, or correction, please drop a comment.