Recently, one of my colleague asked me the difference between the datatypes char, varchar, nchar and nvarchar in SQL Server. 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
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
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:
Write CommentsThanks Sandeep
ReplyDeleteYou are welcome :)
Deletevery nice explanation bro
ReplyDeleteThanks Avinash.
ReplyDelete