Recently I posted a SQL in Sixty Seconds video where I explained how Unicode datatype works, you can read that blog here SQL SERVER – Storing a Non-English String in Table – Unicode Strings. After the blog went live, I had received many questions about the datatypes which can store Unicode character strings.
Honestly, it is very easy to figure out which data types can store Unicode character strings. If you go to Microsoft’s official documentation about Data types (Transact-SQL), you will immediately find the answer to this problem. There are three different kinds of data type which can store the Unicode value.
nchar(n) – Fixed-size string data where n defines the string size in byte-pairs between 1 and 4,000.
nvarchar(n) – Variable-size string data. n defines the string size in byte-pairs between 1 and 4,000.
ntext – Variable-length Unicode data with a maximum string length of 1,073,741,823 bytes. However, we must remember that this data type will be removed from the future version of the SQL Server and it is not recommended to use this datatype. If you are still using this data type, you are recommended to use either nchar or nvarchar based on the business needs.
| Datatype | ISO Synonyms |
| nchar | national char varying |
| nvarchar | national character varying |
| ntext | national text |
The ISO synonyms for nvarchar are national char varying and national character varying. The ISO synonym for ntext is national text. Here is the YouTube Video which started the conversation.
Let me know if you have any questions about the datatypes.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
Hi Sir,
I am working on migration of files from source to destination bucket. Some of the file names are spanish so I kept the field data type as nvarchar. Now the characters looks good in DB but when I read in c# they are not the same as it shows file not found error.
The example file name : Orden señalando vista por videoconferencia González v. AFV.pdf
It is correctly saved in DB
When I read it from DB it prints in console like this(Using C#):
File does not exist. Orden sen~alando vista por videoconferencia Gonza’lez v. AFV.pdf
Could you please assist me on this. Why this is happening?
Thanks