Difference between Char,Varchar and Nvarchar in SQL

                Difference between Char,Varchar and Nvarchar in SQL

Char DataType:

• Char Datatype is used to store fixed length characters. 
• If declared as Char(100) then it will allocate space of memory for 100 characters, no matter how much characters is actually stored in the column

Varchar DataType: 

• Abbreviation for Varchar is Variable length characters
• It is used to store Non-Unicode characters
• Since the name contains 'Variable' it specifies that the memory allocation varies as per the characters stored in the column. For e.g 'Nadir' is stored in the column then the memory allocation would be 5. It allocates 1 byte per character
• Maximum length for this data type is 8000
• Varchar supports collation which requires 1 byte memory storage per characters
• No other language except the once requires 1 byte memory storage per characters can be stored for example: English language characters can be stored in varchar

Nvarchar DataType:

• Abbreviation for Nvarchar is Uni-code variable length characters
• It is used to store Unicode characters
• The memory allocation varies similar to varchar datatype except it allocates 2 byte per characters
• Maximum length for this data type is 4000
• Nvarchar supports all types of collation
• Multiple languages can be stored in nvarchar datatype by using prefix 'N'. Below example explains this point.

                 e.g: Declare @str nvarchar(4000) = N'最低点'

                                                               Highlights:

1. As both varchar and nvarchar datatypes are variable in nature it acquires space as per the characters stored irrespective of the max limit specified
2. It is always recommendable to use varchar compared to nvarchar when the data is in no other language then English
3. Query execution for varchar is generally faster compared to nvarchar