Monday, November 19, 2012

difference between char and varchar

Recently, one of my friend asked me the difference between the datatypes char, varchar, nchar and nvarchar. Also, in the past I have seen the same question being asked in interviews. so, i decided to write a post on the same.

char(n): takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n"). 

varchar(n): takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data

nchar(n) and nvarchar(n)  are exactly same as  char and varchar respectively but it takes exact double spaces to support multilingual language, 1 byte to store Unicode character for each character

Now question is where to use char over varchar? 
Use char dataype only for fixed length column, means, when you know in advance that your data will always be of fixed length. 
For example Phone Number, Country code

declare @Ph_No CHAR(10)
declare @Country_Code CHAR(3)

Now, one more question comes to mind even for fixed length columns, what is the difference char(10) and varchar(10), when both can store 10 bytes of data?
  • char would take 10 bytes while varchar 10+2 bytes
  • Select operation is fast with char columns as compare to varchar
Sample code for reference
declare @char char(5)
set @char = 'ABC' -- 5 bytes
set @char = 'ABCDE' -- 5 bytes

declare @varchar varchar(5)
set @varchar = 'ABC' -- 3 + 2 = 5 bytes
set @varchar = 'ABCDE' -- 5 + 2 = 7 bytes

declare @nchar nchar(5)
set @nchar = 'ABC' -- 5*2 = 10 bytes
set @nchar = 'ABCDE' -- 5*2 = 10 bytes

declare @nvarchar nvarchar(5)
set @nvarchar = 'ABC' -- 3*2+2 = 8 bytes
set @nvarchar = 'ABCDE' -- 5*2+2 = 12 bytes

4 comments: