[Reprint] Cannot resolve collation conflicts between "chinese_prc_ci_as" and "chinese_prc_ci_as_ks_ws" in equal to operations

Original link: http://blog.csdn.net/creathly003/article/details/7331461

I. Error ANALYSIS:
This error is due to inconsistent collation, we do a test, such as:
CREATE TABLE #t1 (
Name varchar (COLLATE) ALBANIAN_CI_AI_WS,
Value int)

CREATE TABLE #t2 (
Name varchar (COLLATE) CHINESE_PRC_CI_AI_WS,
Value int)

After the table is built, execute the connection query:

SELECT * from #t1 A inner joins #t2 B on A.name=b.name

In this way, the error occurs:

Server: Msg 446, Level 16, State 9, line 1
Unable to resolve collation violation for equal to operation.

To exclude this error, the simplest way is to specify its collation when the table joins, so that the error
no longer appears. The statement reads:

Select *
from #t1 A inner join #t2 B
on A.name=b.name collate Chinese_prc_ci_ai_ws
Two. Introduction to collation:
br> What sort of rules? MS is described as: "in Microsoft SQL Server 2000, the physical storage of the
string is controlled by the collation. Collations specify the bit patterns that represent each character and the rules that are used to store the
and compare characters. "
executes the following statement within Query Analyzer to get all the collations supported by SQL Server.

Select * From:: fn_helpcollations ()

Collation name consists of two parts, the first half refers to the character set supported by this collation.
such as:
Chinese_prc_cs_ai_ws
First half: Refers to the Unicode character set, chinese_prc_ the pointer to the mainland simplified Unicode collation.
The second half of the collation is the suffix meaning:
_bin binary sort
_ci (CS) is case sensitive, CI is not distinguished, CS distinguishes
_ai (AS) is accent-sensitive, AI does not differentiate, as is distinguished by
_ki (KS) whether Kana is distinguished Type, Ki does not differentiate, KS differentiates
_wi (WS) Whether the width of the WI is not differentiated, WS-Differentiate

Case-sensitive: If you want comparisons to treat uppercase and lowercase letters as unequal, select this option.
Accent-sensitive: Select this option if you want the comparison to treat accented and non-accented letters as unequal. If you select this option, the
comparison also treats letters with different accents as unequal.
Kana-sensitive: Select this option if you want the comparison to treat katakana and hiragana Japanese syllables as unequal.
Width-sensitive: Select this option


Three if you want the comparison to treat half-width characters and full-width characters as unequal. Application of collations:
SQL Server provides a large number of Windows-and SQL-server-specific collations, but its application is often The
was ignored by the developer. In fact, it is of great use in practice.

Example 1: The contents of the table Name column are sorted by phonetic alphabet:

CREATE TABLE #t (ID int,name varchar (20))
Insert #t Select 1, ' Medium '
UNION ALL Select 2, ' Country '
UNION ALL Select 3, ' People '
UNION ALL Select 4, ' Ah '

SELECT * from #t ORDER by name collate Chinese_prc_cs_as_ks_ws
drop table #t
/* Results:
ID Name
----------- --------------------
4 AH
2 countries
3 people
1 in
*/

Example 2: Make the contents of the table Name column sorted by last name stroke:

CREATE TABLE #t (ID int,name varchar (20))

Insert #t Select 1, ' Three '
UNION ALL Select 2, ' B '
UNION ALL Select 3, ' two '
UNION ALL Select 4, ' one '
UNION ALL Select 5, ' Ten '
SELECT * from #t ORDER by name collate Chinese_prc_stroke_cs_as_ks_ws
drop table #t
/* Results:
ID Name
----------- --------------------
41
+ Plus
32
50
13
*/

Four. Extensions to application of collation rules in practice
The SQL Server Kanji collation can be sorted by pinyin, stroke, and so on, so how do we use this feature
To deal with some of the problems of Chinese characters? I now give an example:

Calculation of Chinese character strokes with collation characteristics

To calculate the stroke of Chinese characters, we have to do the preparatory work, we know that the Windows multi-national character, Unicode currently
A total of 20,902 Chinese characters are included. Simplified GBK Code Chinese Unicode value starts from 19968.
First, we use the SQL Server method to get all the Chinese characters, without the dictionary, we simply use the statement
Can get:

Select Top 20902 code=identity (int,19968,1) to #t from syscolumns A,syscolumns b

With the following statement, we get all the kanji, which are sorted by Unicode value:

Select Code,nchar (code) as Cnword from #t

We then use the SELECT statement to sort it by stroke.

Select Code,nchar (code) as Cnword
From #t
ORDER by NCHAR (code) COLLATE Chinese_prc_stroke_cs_as_ks_ws,code

Results:
Code Cnword
----------- ------
199,681
20008 丨
20022,
20031 丿
20032 Cookbook
20033 Xiha
20057 b
20058 乚
20059 bereaved
20101 sanctioned
19969 Ding
..........

From the above results, we can clearly see that a stroke of kanji, code is from 19968 to 20101, from small to large row, but to
The first word "ding" of two Chinese characters, code 19969, did not start again in order. With this result, we can light
The first or last Chinese character of each stroke is categorized by the SQL statement.
The following sentence is used to get the last kanji:

CREATE table #t1 (id int identity,code int,cnword nvarchar (2))

Insert #t1 (Code,cnword)
Select Code,nchar (code) as Cnword from #t
ORDER by NCHAR (code) COLLATE Chinese_prc_stroke_cs_as_ks_ws,code


Select A.cnword
From #t1 A
Left join #t1 B on a.id=b.id-1 and A.code<b.code
where B.code is null
ORDER BY a.ID

Get 36 Chinese characters, each of which is sorted by Chinese_prc_stroke_cs_as_ks_ws sorting rules
Last Kanji:

Sanctioned 阝 Ma Finglong qi turtle teeth poison ageing niches 龂 nightmares gum looser dragon Yue 龎 bond Qiuyan 龡 kotewall 龝 齹 龣 calls 齈 龞 麷 鸞 麣 龖 龗 齾 snuffle Tantrayana

As can be seen above: "Sanctioned" is the last word after all the Chinese characters are sorted, "阝" is the last of all two Chinese characters sorted
A word ... Wait a minute.
But also found that from the 33rd Chinese character "龗 (33 pen)" After the stroke is a bit messy, incorrect. But it doesn't matter, than the "龗" stroke
More than four characters, we add: 齾 35 pen, Snuffle 36 pen, 靐 39 pen, Tantrayana 64 pen

Chinese Character Stroke Chart (TAB_HZBH):
CREATE TABLE TAB_HZBH (ID int identity,cnword nchar (1))
--Insert the first 33 characters first
Insert Tab_hzbh
Select Top A.cnword
From #t1 A
Left join #t1 B on a.id=b.id-1 and

[Reprint] Cannot resolve collation conflicts between "chinese_prc_ci_as" and "chinese_prc_ci_as_ks_ws" in equal to operations