SQL Server Collation

In the process of using the database, there are always some special needs. Sometimes it is necessary to store Chinese characters, to be case-sensitive or to be sorted in the order of the English. This involves the choice of database arrangement rules.

We can generally choose the database name-right-click (properties)--"function (optiotion)--" Sorting mode (Collation) selection.

* Because there is no Chinese version of the database, Chinese translation may be incorrect.

So what is the difference between these different sorts of methods?

The collation name consists of two parts, and the first half refers to the character set supported by this collation. Such as:

Chinese_prc_ci_as 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 Ordering
• _ci (CS) is case sensitive, CI is not differentiated, CS is distinguished
• _ai (AS) are accent-sensitive, AI not differentiated, as differentiated
• _ki (KS) Whether the kana type is differentiated, KI does not differentiate, KS differentiates
• _WI (WS) Whether the width of the WI is differentiated, WS-Differentiated

Case Sensitivity: Select this option if you want the comparison to treat uppercase and lowercase letters as unequal.

Accent Sensitivity: 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 distinction: Select this option if you want the comparison to treat katakana and hiragana Japanese syllables as unequal.

Width difference: Select this option if you want the comparison to treat half-width characters and full-width characters as unequal.

We can see all the collations in the system function sys.fn_helpcollations. There are 3887 kinds of Server2014 in SQL.

Select *  from Sys.fn_helpcollations ()

In addition to changing the ordering of the entire database, we can also use order by when using the sort method, such as:

Select *  from [dbo]. [cs_appliction]  Order  by [AppName] collate CHINESE_PRC_CS_AS_KS_WS

SQL Server Collation