Difference between VARCHAR vs CHAR data type in SQL Server? (with Example)

 Hello all, today, I am going to share an interesting SQL Server interview question, which will not only help you in your interview but also in your day-to-day work. It explains one of the critical concepts of SQL Server, the difference between VARCHAR and CHAR data type. I am sure, you all have used both of them numerous times but because it's so common many of us ignore the difference between them and when asked to choose between VARCHAR and CHAR on interviews, they fail to give a convincing reason. 

The difference is not just significant from an interview point of view but also from a robust database design because an incorrect choice of data type not only limit what kind of data you can put on but also waste precious space and makes your query slower, particularly when you have to deal with the massive amount of data.

In order to understand the difference, it's also essential to understand the similarity between them so, let's start with that. VARCHAR and CHAR both stores character, text, or String data like name, address, etc. 

One of the important detail to know here is that both stores non-Unicode characters, and there is a separate data type NCHAR and NVARCHAR for storing Unicode characters.

The key difference between CHAR and VARCHAR is that the former is a fixed-length data type while later is a variable-length data type. Yes, the VAR stands for variable length in VARCHAR. 

To give you an example,  CHAR(10) is a fixed-length non-Unicode string of length 10, while VARCHAR(10) is a variable-length non-Unicode string with a maximum length of 10.

This means the actual length will depend upon the data. For example, if you are going to sore a single character string like "Y" then VARCHAR will take less space than CHAR because it will adjust depending upon the length of data.  

Typically, you would use the char if all data values are 10 characters and varchar if the lengths vary.

It's better to use the data type that will take less space. In SQL Server 2005, 2008, 2012, and 2014, NVARCHAR takes more space than VARCHAR data type, almost 2x as much space as VARCHAR.

So, use VARCHAR if you know that all your data would be in ASCII encoding, but if you are going to store Unicode strings like storing data from different languages, then you need to use NVARCHAR to support Unicode data. 

NVARCHAR is a must if you intend to support internationalization (i18n). You can further join these Microsoft SQL Courses to learn more about char, varchar, nchar, and nvarchar data type in SQL.

Similarities between CHAR vs. VARCHAR in SQL

Now, that you understand the fundamental similarity and differences between char and varchar, let's see some more important points for revision:

1) Both stores non-Unicode characters.
2) Both are character data types.
3) Both take 1 byte to store one character.

4) The maximum length of both CHAR and VARCHAR data types is 8000 characters in SQL Server. Maximum length is defined in parenthesis, e.g. maximum length of CHAR(6) is 6 characters, and the maximum length of VARCHAR(6) is also 6 characters.

The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

DECLARE @abc AS CHAR (8000);

DECLARE @abcd AS VARCHAR (8000);

Don't confuse length and size here, the length here represents how many characters a CHAR or VARCHAR variable can take, and size represents the storage bytes they take to store those characters. See these best SQL and Database courses to learn more about SQL fundamentals like this from the Microsoft SQL server perspective.

CHAR vs. VARCHAR in SQL Server

And, here are some of the key differences between CHAR and VARCHAR data types in SQL

1. Fixed vs Variable storage

 CHAR is a fixed storage data type, but VARCHAR is a variable storage data type. What this means is that the storage size of the CHAR column or variable is always fixed, as specified initially but the storage size of the VARCHAR column or variable depends upon actually stored data.

For example, if you create a variable of type CHAR(6) then it will always take 6 bytes, whether or not you store six characters ( 1 byte per character) but VARCHAR(6) column can take anything between 2 to 8 bytes. 2 bytes are additional overhead, and 1 to 6 bytes are actual storage depending upon how many characters you store.

2.Usage

You should use CHAR when your data is of fixed length, like telephone numbers, zip code, cc number, ba num, ss numbers, etc. Another use of CHAR data type is storing boolean columns like 'Y' and 'N'.

You can use the VARCHAR type column to store things that are not of fixed length like name, comment, etc. 

3. Storage

CHAR variables always take the same storage space irrespective of the number of characters actually stored, while the VARCHAR variable's storage size depends upon the actual number of characters stored.

4. Space Overhead

 VARCHAR data type has an overhead of 2 bytes as compared to CHAR variables. This means if your data is always fixed length, then storing them into VARCHAR will take more space than CHAR.

5. Padding

In the case of CHAR, data are padded to make specific characters long, no padding is done on VARCHAR columns

6. Null

A CHAR column cannot hold a NULL, so behind the scene, SQL will actually use a VARCHAR field like CHAR(x) NULL column is actually a VARCHAR(x) column.

7. Reservation

CHAR reserves storage space, VARCHAR doesn't

8. Index

Use of index can fail if you provide wrong data type like in SQL Server when you have an index over a VARCHAR column and present it a Unicode String, MSSQL Server will not use the index.


That's all about the difference between CHAR and VARCHAR data types in SQL. In short, CHAR is a fixed-size data type, while VARCHAR is a variable-size data type, where actual storage space depends upon an actual number of characters stored in the column. 

You should always use the right data type to minimize storage requirements. Using incorrect data types not only results in wasted space but also affects the performance of the SQL query.

Other SQL Server Interview Questions you may like

• Difference between a table scan and an index scan in SQL Server? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• The difference between TRUNCATE and DELETE in SQL? (answer)
• The difference between self and equi-join in SQL? (answer)
• Top 5 SQL and Database Course for Programmers (courses)
• The difference between WHERE and HAVING clause in SQL? (answer)
• The difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
• How to find duplicate records in a table? (query)
• Difference between isNull() and collasce() in SQL Server? (answer)
• 10 SQL Queries from Programming Job Interviews (queries)
• 5 Courses to Learn SQL Server and T-SQL (Courses)

Thanks for reading this article so far. If you like this SQL Interview question and my explanation, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you need more SQL Interview Questions for practice, you can also check out these SQL Phone interview questions which not only tell you the common SQL questions but also explain the answer in detail to fill gaps in your learning.