chevron_left
399 points
8 4 3

What is the difference between varchar and nvarchar

CHAR vs VARCHAR


Talking about the CHAR data type:
  • It is a fixed length data type
  • Used to store non-Unicode characters
  • Occupiers 1 byte of space for each character

If the value provided to a variable of CHAR data type is shorter than the length of a column of declared the size of the variable, then the value would be right-padded with blanks to match the size of column length.

  1. DECLARE @string CHAR(20)  
  2. SET @string = 'Robin'  
  3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

As you can see above, the bytes occupied by the variable are 20 even though the length of the characters is 5. That means that irrespective of the character stored in the column, it will occupy all bytes to store the value.

About the VARCHAR data type:

  • It is a variable length data type
  • Used to store non-Unicode characters
  • Occupies 1 byte of space for each character
  1. DECLARE @string VARCHAR(20)  
  2. SET @string = 'Robin'  
  3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

As you can see above, it is showing DATALENGTH as 5 which means it will use only the number of bytes equal to the number of characters. This will allow me to avoid wasting database space.

More Posts

what is the difference between char and varchar? Being human - Jun 8
Difference between varchar and nvarchar in SQL Hafsa_Rao - Oct 1
What is the difference between varchar and varchar2 amna - Mar 3
What is the difference between ascii and unicode Sanjana Sagar - May 28
What is the difference between thread and process Sanjana Sagar - May 28
What is the difference between a stack and a queue Sanjana Sagar - May 28
What is the difference between id and class in css Sanjana Sagar - May 22
What is the difference between pre and post Sanjana Sagar - May 20
What is the difference between oltp and olap Sanjana Sagar - May 20
What is the difference between abstraction and encapsulation in java Sanjana Sagar - May 20