SQL Server CHAR VS NCHAR

What is the difference Between CHAR and NCHAR data types?

CHAR

• CHAR data type is used to store non-Unicode string data of fixed-length.
• Stores data at 1 byte per character.
• Supports up to 8000 characters.

Syntax
char(n)

n specify the string length that ranges from 1 to 8000. Default value for n is 1.

NCHAR

• NCHAR data type is used to store Unicode string data of fixed-length.
• It stores data at 2 byte per character.
• It supports up to 4000 characters.

Syntax
nchar(n)

n specify the string length that ranges from 1 to 4000. Default value for n is 1.

Lets Look at an example to demonistarte the difference between CHAR vs NCHAR

Data Storage Vs Length

• CHAR

Here we have a variable @strtxt of datatype char of size 15.

DECLARE @Str CHAR(15)
SET @Str = 'Microsoft'
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength ,
LEN(@Str) AS StrText_length

As char has fixed-length and takes 1 bytes per character to store the data so it takes 15 bytes to store @strtxt value ‘microsoft’  ,even though the length of @Strtxt ‘microsoft’  is 9 .

So the  data storage lenght of characters will be the fixed-lenght of variable @strtxt * 1 bytes (15 *1) =15.

• NCHAR

We have a variable @strtxt of datatype char of size 15.

DECLARE @Str NCHAR(15)
SET @Str = 'Microsoft'
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength ,
LEN(@Str) AS StrText_length

As nchar has fixed-length and takes 2 bytes per character to store the data so it takes 30 bytes to store @strtxt value ‘microsoft’  ,even though the length of @Strtxt ‘microsoft’  is 9 .

So the  data storage lenght of characters will be the fixed-lenght of variable @strtxt * 2 bytes (15 *1) =30.

Can store Unicode Data ?

• CHAR

Char can not store unicode data , here we try to store hindi language text to @str variable of type char.

DECLARE @Str CHAR(15)
SET @Str = N'माइक्रोसॉफ्ट'
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength ,
LEN(@Str) AS StrText_length

 

As you can see, it does not support unicode characters /multilingual data.

• NCHAR

NCHAR can store unicode data , again we try to store hindi language text to @str variable of type nchar.

Note: Always make sure that you prefix Unicode string literals with an N prefix.

DECLARE @Str NCHAR(15)
SET @Str = N'माइक्रोसॉफ्ट'
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength ,
LEN(@Str) AS StrText_length

 

Recommended

SQL Server VARCHAR VS NVARCHAR

How to get the detailed information of all datatypes in SQL Server ?