Nchar Vs Char Data Types In SQL Server

Nchar vs Char in SQL Server

In my earlier article, I’ve gone through the difference between varchar and nvarchar and between char and varchar. In this article, I’ll go over nchar vs char data types. The char and nchar string data types have many similarities and some differences. Here I’ve compiled the similarities, differences, advantages, disadvantages and the usage of char and nchar. These comparisons will help you to understand and use them appropriately in your applications and programs.

Similarities Between Nchar And Char

  1. Both char and nchar are Fixed length string data.
  2. Their maximum storage capacity is 8000 bytes.
  3. Both has an optional argument specified as “n”. i.e. char [(n)] and nchar [(n)]. “n” defines the length of the string.
  4. While declaring a variable without specifying the argument “n”, the default length is 1.
  5. If, n is not specified while using along with CAST function, the default length is 30.
  6. Unlike varchar and nvarchar, the space used by char and nchar is fixed as defined irrespective of the length of the string stored in them. They will reserve the storage space for the size you have declared.
  7. Both the string data types can be used to store non-Unicode strings.
  8. Index can be created on both the data types.

Example: Usage Of Char & Nchar

/* Using Char */
Declare @c as Char(30);
Set @c = 'Have a good day!';
Select @c;

/*Results:*/
------------------------------
Have a good day!              

(1 row(s) affected)

/* Using Nchar */
Declare @n as Nchar(30);
Set @n = 'Have a good day!';
Select @n;

/*Results:*/
------------------------------
Have a good day!              

(1 row(s) affected)

Char Vs Nchar: Differences

#

Char

Nchar

1 Can store only non-Unicode string data. Can store Unicode string data. It stores data in the form of UNICODE UCS-2 characters.
2 It just needs 1 byte to store a character. It needs 2 bytes to store a character.
3 The storage size is equal to the size defined while declaring the variable. For example, Declare @c As Char(20) means the storage size is 20 characters or 20 bytes. The storage size is equal to double the size defined while declaring the variable. For example, Declare @c As Nchar(20) means the storage size is 40 characters or 40 bytes.
4 Can store up to 8000 characters. Can store only up to 4000 characters.

Example: Difference in storage size

If you notice the below example, the char and nchar variables are declared with the size of 30 bytes. The string Have a good day! has only 16 characters. When you check the storage data size using DATALENGTH, it will be 30 for char and 60 for nchar. This is because both these data types are of fixed length and nchar uses double the size declared.

Declare @c as Char(30);
Declare @n as Nchar(30);
Set @c = 'Have a good day!';
Set @n = N'Have a good day!';
Select DATALENGTH(@c), DATALENGTH(@n);

/*Results:*/
----------- -----------
30          60

(1 row(s) affected)

Nchar Vs Char: Advantages And Disadvantages

Char

Nchar

Advantages Occupies less physical storage space as it’s storage length is equal to the size defined while declaring the variable. Supports multiple languages and locales. So any Unicode string data can be stored without worrying about conversion.
Disadvantages Need to use conversion technique if you want to store Unicode date in varchar column. Occupies more physical storage space as it’s storage length is double the size defined while declaring the variable.
Usage Useful when the data you are planning to store is just non-Unicode ASCII character set. Useful if you are planing to store data of different language or locale. It’s good to use Nchar, in case if you have any feature plan to include Unicode string to be stored in a column.

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.