SQL Server sorting rules

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.