The difference between nvarchar and varchar

Time:2021-6-4

First of all, you need to know about Unicode. Here is Baidu Encyclopedia’s explanation of Unicode.
        Unified code, also known as universal code and Unicode, is an industry standard in the field of computer science, including character set, coding scheme, etc.
        Unicode is produced to solve the limitations of traditional character encoding schemes. It sets a unified and unique binary encoding for each character in each language to meet the requirements of cross language and cross platform text conversion and processing.
        Speaking human language means that you use English, I use Chinese, and he uses Japanese, but they all want to express the same thing. At this time, a big brother stood up and said: I’ll work out a unified rule. In the future, you all follow this standard, and there will be no cross platform and cross language mistakes. The Unicode character set is then generated.
        Two bytes is enough to hold most of the text in all the languages in the world. Now we use ucs-2, that is, two byte encoding, while ucs-4 is developed to prevent two bytes from being insufficient in the future.
Here we have to introduce the knowledge of characters and bytes. The following is the introduction of rookie tutorialhttps://www.runoob.com/w3cnote/byte-character.html
       Generally, in English, a letter or character occupies one byte, and a Chinese character is represented by two bytes.
        In Unicode coding, an English word is one byte, and a Chinese word is two bytes
varchar(n)
        Variable length non Unicode character data of N bytes in length. N must be a number between 1 and 8000. The storage size is the actual length of the bytes of the input data, not n bytes.
nvarchar(n)
        Variable length Unicode character data containing n characters. The value of N must be between 1 and 4000.
For specific storage, see the following experiments:
DECLARE
        @Str_varchar AS VARCHAR(8000)
SET
        @ Str_ Varchar =’test abc123 ‘
SELECT
        DATALENGTH(@Str_ Varchar) as n ‘number of bytes’,
        LEN(@Str_ Varchar) as n ‘number of characters’

DECLARE
        @Str_nvarchar AS NVARCHAR(4000)
SET
        @ Str_ Nvarchar =’test abc123 ‘
SELECT
        DATALENGTH(@Str_ Nvarchar) as n ‘number of bytes’,
        LEN(@Str_ Nvarchar) as n ‘number of characters’

img

       Conclusion: I recommend nvarchar in development