Saturday, January 21, 2017

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

One of the frequently ask SQL questions in any programming interviews 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 the space? Is it better to use a CHAR variable instead of VARCHAR(2) and why? These are some of the really interesting question and more meaningful than classical SQL questions like the difference between char vs varchar data type question.

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

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

One of the first question 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 interviewer mean 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 a 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 stores 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 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 what is limit or capacity of 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 further read Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about this concept.

How many characters you can store into VARCHAR Column or Variables in SQL Server

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 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 right data type can be significant for large tables with millions of records. You can read Querying Microsoft SQL Server 2012 Training Kit for Exam 70-461 to learn more about the difference between char and varchar data type in SQL.

How many characters you can store into VARCHAR variable in SQL Server

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 question on this concept fell 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 with your friends and colleagues, it makes a lot of difference. If you have any suggestion, feedback or correction, please drop a comment. 

No comments :

Post a Comment