SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage')
Here are the query results:
936 Chinese Simplified GBK
950 Chinese Traditional BIG5
437 US/Canadian English
932 Japanese
949 Korean
866 Russian
65001 unicode UFT-8
When installing SQL SERVER 2008, because we chose the default installation, we created itWhen database, the default sorting rule selected by the server is a SQL_Latin1_General_CP1_CI_AS collation rule, so that when inserting text into the data table, it will not be displayed normally, all with "? instead.
Then, when we create a database, we need to manually specify a collation rule, which can be selected as Chinese_PRC_CI_AS collation, as shown in the figure below
Sorting rules:
The sorting rules used in SQLSEVER2005 are SQL_Latin1_General_CP1_CI_AS, not the three sorting rules that can display simplified Chinese characters correctly:
Chinese_PRC_BIN,
Chinese_PRC_CI_AS,
Chinese_PRC_CS_AS。
Take a look at MS's explanation of collation: Collation Rules Control Physical Storage of Strings in SQL Server 2005. Collation rules specify the bit pattern that represents each character and the rules for storing and comparing character usage.
That is, in SQLSERVER, the collation is actually the character encoding.
By executing the following statement in the query analyzer, you can get all the collation rules supported by SQL SERVER.
select * from ::fn_helpcollations()
The collation name consists of two parts, the first part refers to the character set supported by this collation.
For example: Chinese_PRC_CS_AI_WS
The first half refers to the UNICODE character set, and Chinese_PRC_refers to the sorting rules for the simplified Chinese character UNICODE.
The second half of the collation is the meaning of the suffix:
_BIN Binary sorting
_CI(CS) Whether it is case-sensitive, CI is not sensitive, and CS is sensitive
_AI (AS) Whether to distinguish between accents, AI does not distinguish, AS distinguishes
_KI(KS) Whether to distinguish between pseudonym types, KI does not, KS distinguishes
_WI(WS) is not width-distinguished, WS is not differentiated
Case sensitive: Select this option if you want comparisons to treat uppercase and lowercase letters as unequal.
Distinguish between accents: Select this option if you want comparisons to treat accented and unstressed letters as unequal. If you select that option, the comparison also treats letters with different accents as unequal.
Distinguish Kana: Select this option if you want the comparison to treat Katakana and Hiraka Day syllables as unequal.
Width differentiation: Select this option if you want the comparison to treat half-width and full-width characters as unequal.
After understanding the collation rules in SQLSERVER, the following conclusions can be drawn for the above problem:
1. Modify the SQLSERVER user database to support the collation of Chinese character sets.
2: For Chinese characters display??, but do not want to change the sorting rules on the database, and want to display Chinese characters correctly, it is recommended to use all Unicode type fields in the design, that is, those field types starting with N, such as nChar, nVarchar, to display Chinese characters correctly.
3: If you don't want to change the collation rule or change the field type, then you need to change the SQL statement, and for all Chinese characters, you must also add N in front to display it correctly. Please refer to the following two statements for specific methods:
Query: select * from tb_Cust where FirstName=N'Wang'
Insert: insert tb_Cust(FirstName,LastName,Sex) values(N'Wang',N'Xinhao',N'Male')
Note: For the future, it will be more troublesome to modify the server-level collation rule after SQLSERVER2000, and it is necessary to rebuild the master database.
Currently, for databases that have already been created, we can also change the collation method in the options page in the properties window of the database, so that when inserting text into the data table, there will be no error messages!
|
Posted on 10/27/2016 12:50:16 PM
|
|
|
|
