Difference between char, varchar, nchar and nvarchar data types in SQL Server? Example

What is the difference between char and varchar in SQL, followed by nchar and nvarchar, is one of the popular SQL interview questions, and surprisingly not every programmer knows this basic difference. If you go with the name, which you should, then you can figure out that char is a fixed-length data type while varchar should be a variable-length data type. Though all char, varchar, nchar, and nvarchar are used to store text or String data there are subtle differences between them. As I said char is fixed length, which means a variable or a column like Zipcode char(10) will take only 10 bytes to store data, including space.

On the other hand, a varchar variable or column will take variable space, depending upon data you store + 2 additional bytes for storing length as I explained in my earlier post about how much space varchar variable takes in disk . For example, a varchar column name varchar name(20) will take 6 bytes if you store "Jack" (4 + 2) and 7 bytes if you store "Jones" (5 + 2).

In order to get better performance, you should use char for fixed-length columns like the zipcode, where every row is under a certain length e.g. 6 for India, and 5 + 4 digits postal codes for the USA.

On the other hand, for a variable-length column, it's better to use varchar data type to save the space,  which is lost in the case of char type, if actual data is always way less than capacity.

In particular, this question is next in a series of a couple of popular SQL interview questions, e.g. difference between WHERE and HAVING clause and writing SQL query to join three tables. If you come across any other interesting SQL queries then you can also share with us, if you don't know the answer, we will try to find out together.

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 these SQL Server online courses. It's a great resource to start with T-SQL and SQL queries in SQL Server.

And, if you in rush, here is a nice table which summarizes the difference between all the SQL string types like char, varchar, nchar, and nvarchar in a structured way, you can refer this table before going for any interview. 

How to use NCHAR vs NVARCHAR in SQL Example

Now let's see the difference between nchar and nvarchar data types in SQL. nchar(n) and nvarchar(n) are similar to their counterpart char and varchar, but they take twice as space as them, to support multilingual languages. 

Additional space is used to store Unicode characters for each character, which means 2 bytes for each character, 1 for character + 1 for Unicode.

Apart from this fundamental question, you often see a couple of follow-up questions like when to use char and varchar in the database? or more frequently what is the difference between char(20) and varchar(20) variables, as they can only store 20 bytes to store data.

Well, the main difference is that varchar might take fewer bytes, depending upon the length of data you store on it. 

For example, if we store "USA" in a variable of type char(20) and varchar(20) then first will take 20 bytes, while the second will take only 5 bytes (3 +2 ), but the performance of char columns are better in SELECT query than varchar columns.

Similarly, if we use nchar(20) and nvarchar(20) for storing the "SQL" string then the first will take 40 bytes (2*20) and the second variable will take 8 bytes (3*2 +2).

Here is a summary of how much space a char(10), varchar(10), nchar(10) and nvarchar(10) variable takes for storing same data :

declare @cData char(10)

set @cData = 'SQL' -- 10 bytes

set @cData = 'Java' -- 10 bytes

declare @vcData varchar(10)

set @vcData = 'SQL' --  3 + 2 = 5 bytes

set @vcData = 'Java' -- 4 + 2 = 6 bytes

declare @ncData nchar(10)

set @ncData = 'SQL' -- 10*2 = 20 bytes

set @ncData = 'Java' -- 10*2 = 20 bytes

declare @nvcData varchar(10)

set @nvcData = 'SQL' -- 3*2+2 = 8 bytes

set @nvcData = 'Java' -- 4*2+2 = 10 bytes

Thanks, guys, that's all on the difference between char and varchar data types in SQL and nchar vs nvarchar. Simple rule of thumb is, use char for fixed column length and use varchar for variable column length, and use nchar or nvarchar if you want to store unicode data which is good for storing international data like international names and address. 

Similarly char is good for storing zipcode, phone numbers, social security numbers etc while varchar is good for storing names and addresses.

 I hope this knowledge of basics, not only helps in SQL interviews but also choosing the right type for your columns in tables and variables in your stored procedures. 

Let me know if you have any doubts or questions on char or varchar variables.

Other related SQL queries, Interview questions, and articles:

• 12 Database Index Questions from interviews (index questions)
• How to find the second highest salary in a table? (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Difference between the Unique and Primary keys in the table? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• Write a SQL query to copy or backup a table in MySQL (solution)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Top 5 Books to learn Advanced SQL and Database Design (books)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• 5 Best PostgreSQL Courses for Beginners (online courses)
• Difference between View and Materialized View in Database? (answer)
• 10 Free SQL and Database Courses for Beginners (free courses)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)

Thanks for reading this article so far. If you like these TSQL and SQL Server Interview question answers then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.

P.S. - If you are interested in learning Microsoft SQL Server in depth and looking for best resources to start your journey then you can also checkout these Microsoft SQL Server online courses to learn T-SQL and SQL Server in depth.