Acrobatics field--special characters/uncommon words and varchar

For the Chinese version of SQL SERVER, the default collation used after the default installation is Chinese_prc_ci_as, under which the varchar type can be used to "normally access" the characters in the text and some Southeast Asian countries. While the varchar type saves half of the storage space in English characters and numbers than nvarchar, many DBAs are accustomed to using varchar types to hold character data, but there are some garbled pitfalls!

The first is the special word such as or copyright character, the test code is as follows:

--Prepare the test tableDROP TABLETB1GOCREATE TABLETB1 (C1VARCHAR( $), C2NVARCHAR( $))GO--inserting test DataINSERT  intoTB1 (C1,C2)SELECTN'm²'N'm²'UNIONSELECTN'®'N'®'--EnquirySELECTC1,CAST(C1 as NVARCHAR( $)) asC1_n,c2,CAST(C2 as VARCHAR( $)) asC2_v fromTB1

The test results are as follows:

It is obvious that the superscript is converted to the normal number 2 under the varchar type, while the copyright symbol is garbled directly under the varchar type.

For these special characters, may not be used, such as the User Name field, then you can use the varchar type?

Of course not, can avoid special characters, but also have to consider "cultural parents" to the children to some rare words to show a culture!!! For example, five dynasties, founder of South Han, Liu, who thinks he is a bull, has created a "?" Word, take meaning for the dragon in the sky, so the meaning of the Ox fork does not recruit varchar "like", test code as follows:

INSERT  intoTB1 (C1,C2)SELECTN'Liu?'N'Liu?'SELECTC1,CAST(C1 as NVARCHAR( $)) asC1_n,c2,CAST(C2 as VARCHAR( $)) asC2_v fromTB1

The results appear as follows:

“?” Words can only be properly displayed in nvarchar mode ha!

It is recommended to use nvarchar to hold non-English character data for reasons:
Reason 1:varchar There is a problem with garbled characters or characters being changed when storing special or uncommon words
Reason 2: For Chinese characters, using varchar and nvarchar consumes the same space, for English characters, the use of varchar is less than nvarchar, but as the cost of disk becomes lower, its performance and cost savings are limited. (Exception: If there are a large number of English characters and a small number of non-English characters in the data, the varchar type can be considered)
Reason 3: For enterprises needing internationalization, it is too expensive or difficult to upgrade varchar to nvarchar later.
Reason 4: When using varchar to store non-English characters, it is easy to generate erroneous pre-valuations, especially when you are performing a prefix-matching estimate like this.

========================================

Acrobatics field--special characters/uncommon words and varchar