SQL Server sorting rules

Syntax

<SQL_collation_name> :: =      SQL_SortRules[_Pref]_CPCodepage_<ComparisonStyle><ComparisonStyle> ::=        _CaseSensitivity_AccentSensitivity | _BIN

Parameters

Sortrules

A string that identifies the alphabet or language. It is used to sort by dictionary. For example, latin1_general or polish, the simplified Chinese is chinese_prc, and the traditional Chinese is chinese_taiwan.

Pref

Specifies that uppercase letters take precedence.

CodePage  

1-4-digit number used to identify the code page used by the sorting rule. CP1 specifies the code page 1252. For all other code pages, you must specify the complete code page number. For example, cp1251 specifies the code page 1251 and cp850 specifies the code page 850.

Casesensiti.pdf_ Ci (CS)

The CI parameter is case insensitive and the CS parameter is case sensitive.

Select this option if you want to compare uppercase and lowercase letters.

Distinguish between uppercase and lowercase letters. If this option is selected, lowercase letters are sorted before the corresponding upper-case letters.

If this option is not selected, SQL Server considers the uppercase and lowercase letters to be the same for sorting purposes.

Accentsensiti.pdf_ AI ()

AI specifies no accent, and as specifies the accent.

Select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected, the comparison also treats letters with different accents as unequal.

Distinguish between accent and non-accent characters. For example, "a" and "?" It is considered as a different character.

If this option is not selected, SQL Server treats the English letters as both accent and non-accent in order.

_ KI (KS)

Whether to distinguish the Kana type. Ki is not differentiated, and KS is differentiated.

Distinguish two types of Kana characters in Japanese: hirakana and katakana.

_ Wi (WS)

Whether to differentiate width WI and WS.

Identifies the single-byte form and double-byte form of a character.

If this option is not selected, SQL Server regards the single-byte format of the same character as the double-byte format during sorting.

 Bin

Specify the binary sorting order.

Binary sorting rules sort data based on the encoding values defined by region settings and data types. The binary sorting rules in SQL Server force the use of binary sorting order, defines the language region settings to use and ANSI code page. Because binary sorting rules are relatively simple, they are very useful for improving application performance. For non-Unicode data types, data comparison is based on the Code bit defined in the ANSI code page. For Unicode data types, the data comparison is based on the Unicode code bit. For binary sorting rules of Unicode data types, data sorting does not take the region settings into consideration. For example, if latin_1_general_bin and japanese_bin are applied to Unicode data, identical sorting results are obtained.

The early binary sorting rules in SQL Server perform incomplete bitwise Comparison on Unicode data. Earlier SQL Server binary sorting rules compared the first character as wchar, followed by byte comparison. To achieve backward compatibility, the existing binary sorting rule semantics will not be changed.

The following is a combination of sorting rules:

_ Bin1	Binary sorting.
_ Bin21	Binary-bitwise sorting order (new feature of SQL Server 2005 ).
_ Ci_ai	Case-insensitive, accent-insensitive, Kana-insensitive, and halfwidth.
_ Ci_ai_ks	Case-insensitive, accent-insensitive, Kana-differentiated, and full-width
_ Ci_ai_ks_ws	Case-insensitive, accent-insensitive, Kana-differentiated, full-width
_ Ci_ai_ws	Case-insensitive, stress-insensitive, Kana-insensitive, full-width
_ Ci_as	Case-insensitive, stress-sensitive, Kana-insensitive, full-width
_ Ci_as_ks	Case-insensitive, stress-sensitive, Kana-specific, and full-width
_ Ci_as_ks_ws	Case-insensitive, accent-sensitive, Kana-specific, and halfwidth-sensitive
_ Ci_as_ws	Case-insensitive, stress-sensitive, Kana-insensitive, full-width
_ Cs_ai	Case-sensitive, stress-insensitive, Kana-insensitive, full-width
_ Cs_ai_ks	Case-sensitive, stress-insensitive, Kana-sensitive, and full-width
_ Cs_ai_ks_ws	Case-sensitive, accent-insensitive, Kana, and halfwidth
_ Cs_ai_ws	Case-sensitive, stress-insensitive, Kana-insensitive, full-width
_ Cs_as	Case Sensitive, stress sensitive, no Kana, no full halfwidth
_ Cs_as_ks	Case Sensitive, stress sensitive, Kana sensitive, full-width
_ Cs_as_ks_ws	Case Sensitive, stress sensitive, Kana sensitive, and halfwidth sensitive
_ Cs_as_ws	Case Sensitive, stress sensitive, no Kana, full halfwidth Differentiation

1. If bin or binary-bitwise is selected, the options are case sensitive, accent sensitive, Kana sensitive, and halfwidth sensitive.

For Simplified Chinese: SQL-server is sorted by pinyin by default. The following sorting methods are used by default in SQL-Server:

196	Chinese_taiwan_stroke_bin
197	Chinese_taiwan_stroke_ci_as
198	Chinese_prc_bin
199	Chinese_prc_ci_as

_ Stroke is sorted by strokes. To sort strokes in simplified Chinese, you can use

Chinese_prc_stroke_cs_as_ks_ws and so on. Then, select _ cs_as_ks_ws as needed.

 

 

From: http://blog.csdn.net/rxr1st/archive/2008/03/14/2179933.aspx