Difference between char, varchar, nchar and nvarchar data type in SQL Server database

What is difference between char and varchar in SQL, followed by nchar and nvarchar, is one of the popular SQL interview question, and surprisingly not every programmer knows this basic difference. If you go with name, which you should, than you can figure out that char is a fixed length data type while varchar should be 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 e.g. 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. 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 e.g. zipcode, where every row are under certain length e.g. 6 for India, and 5 + 4 digits postal codes for 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 case of char type, if actual data is always way less than capacity. In particular this question is next in series of couple of popular SQL interview question, 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 than you can also share with us, if you don't know answer, we will try to find out together.

NCHAR vs NVARCHAR in SQL

Now let's see difference between nchar and nvarchar data types in SQL. nchar(n) and nvarchar(n) are similar to there counterpart char and varchar, but they take twice space than them, to support multilingual languages. Additional space is used to store Unicode character for each character, which means 2 bytes for each character, 1 for character + 1 for Unicode. Apart from this fundamental question, you often see couple of follow-up questions like when to use char and varchar in database? or more frequently what is difference between char(20) and varchar(20) variables, as they can only store 20 bytes to store data. Well, main difference is that varchar might take less bytes, depending upon length of data you store on it. For example if we store "USA" in a variable of type char(20) and varchar(20) than first will take 20 bytes, while second will take only 5 bytes (3 +2 ), but performance of char columns are better in SELECT query than varchar columns. Similarly if we use nchar(20) and nvarchar(20) for storing "SQL" string than first will take 40 bytes (2*20) and 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 difference between char and varchar data types in SQL and nchar vs nvarchar. I hope this knowledge of basic, not only helps in SQL interviews but also choosing right type for your columns in tables and variables in your stored procedures. Let me know if you have any doubt or question on char or varchar variables.

Source:http://javarevisited.blogspot.com/2013/11/difference-between-char-varchar-nchar-nvarchar-sql-database.html