CHAR vs VARCHARNCHAR vs NVARCHAR

This small article is intended for the audience stuck in their interview when asked for the differences among CHAR, VARCHAR, NCHAR and NVARCHAR data types. Actually it is simple but sometimes people get confused.

  • CHAR vs VARCHAR
  • NCHAR vs NVARCHAR

Considering an example, we will look into each one of them.

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

Note: The LEN() method provides the length of a character excluding trailing blanks stored in the string expression whereas the DATALENGTH() method provides the number of byte spaces occupied by the characters in a string expression.

As you know we represent the character values within single quotes, for example ‘Robin’. But do you know we can represent these same characters within double quotes similar to programming languages representing a string, for example “Robin”? This can be done by setting the value:

1. SET QUOTED_IDENTIFIER OFF

By default, it is set to ON

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’

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’ 

Note:  If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a CHAR column defined as NULL is considered as VARCHAR.