Collate clause sorting rules for SQL statements

Collate is a clause that can be applied to database or column definitions to define sorting rules, or to string expressions to apply sorting rule conversion.

Syntax

Collate {<collation_name> | database_default}

<Collation_name >::=

{Windows_collation_name} | {SQL _collation_name}

Parameters

Collation_name

Name of the sorting rule applied to expressions, column definitions, or database definitions. Collation_name can be specified only
Windows_collation_name or SQL _collation_name. Collation_name must be a text value.
Collation_name cannot be expressed by variables or expressions.

Windows_collation_name is a Windows sorting rule.

Name of the sorting rule.

SQL _collation_name is used for SQL sorting rules

Name of the sorting rule.

Database_default

Make the Collate clause inherit the sorting rules of the current database.

 

The following describes what sorting rules are. SQL Server 2005 provides two sets of sorting rules: Windows sorting rules and SQL sorting rules.

Windows sorting rules

Windows sorting rules are the sorting rules defined for SQL Server. They are used to support Windows region settings. Number of Windows regions
SQL Server has many sorting rules in windows. Windows
The region name is based on the language and region. For example, French (Canada ). However, several languages can share the common alphabet and rules for character sorting and comparison. In addition, Windows
The region settings also specify SQL Server windows
Sorting rules do not have attributes, such as currency, date, and time format. Because different countries and regions (such as the UK and the US) use different currency, date, and time formats, they need different
Windows sorting rules. However, because they use the same alphabet and rules to sort and compare characters, the same SQL Server sorting rules can be used.

SQL sorting rules

An SQL sorting rule is a compatibility option used to match SQL Server
Common combination attributes of code page numbers and sorting order specified by earlier versions. Many sorting rules support specifying the suffix to distinguish between upper and lower cases, accents, Kana, and halfwidth, but not all. In SQL
In Server 2005, Windows sorting rules should be prioritized. Especially in databases, Unicode columns and non-Unicode Columns
When all columns exist. In Windows, Unicode-based sorting rules are applied to both Unicode and non-Unicode data. This indicates
SQL Server converts non-Unicode data to Unicode for comparison. This ensures that SQL Server
The consistency of all data types in SQL Server also enables developers to sort strings using the sorting rules in SQL Server. On the other hand, for Unicode
After the corresponding windows sorting rules are used for data, the SQL sorting rules can apply non-Unicode sorting rules to non-Unicode data and Unicode
Unicode sorting rules are applied to data. This difference may result in inconsistent comparison of the same characters. Therefore, if your database contains both Unicode columns and non-
Unicode columns should be defined as using Windows sorting rules to use the same sorting rules for Unicode and non-Unicode data.

Note: The collate clause can only be applied to Char, varchar, text, nchar, nvarchar, and ntext data types.

You can run the fn_helpcollations function to retrieve the list of all valid sorting rule names for Windows and SQL sorting rules:

Select *

From fn_helpcollations ()

The following table describes the sorting order options of the Windows sorting rules of SQL Server 2005.

Sort order (suffix)	Sort order description
Binary (_ bin) 1	SQL Server Sort and compare the data in the table. The binary sorting order is case sensitive and accent sensitive. Binary sorting is also the fastest. For more information, see use binary sorting rules. If this option is not selected, SQL Server will follow the sorting and comparison rules of the relevant language or alphabet defined in the dictionary.
Binary-bitwise (_ bin2) 1	Sort and compare the data in the SQL Server table based on the Unicode code bit of the Unicode data. For non-Unicode Data, binary-bitwise will use the same comparison method as binary sorting. The advantage of Binary-bitwise sorting is that the sorted SQL Server Applications that compare data do not have to sort data again. Therefore, the binary-bitwise sorting sequence simplifies application development and improves performance. For more information, see use binary sorting rules.
Case Sensitive (_ CS)	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.
Distinguish accent (_)	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.
Distinguish Kana (_ KS)	Distinguish two types of Kana characters in Japanese: hirakana and katakana. If this option is not selected, SQL Server considers that Katakana and katakana are of the same purpose for sorting.
Differentiate full halfwidth (_ 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.

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.
For example:

Chinese_prc_cs_ai_ws

First half: the Unicode character set. The chinese_prc _ pointer sorts Unicode in simplified Chinese characters.

The second half of the sorting rule is the suffix meaning:

_ Bin binary sorting

_ Ci (CS) is case sensitive, CI is case insensitive, and CS is case sensitive

_ Whether AI (AS) distinguishes stress, AI does not distinguish,

_ KI (KS) indicates whether Kana is distinguished. Ki is not distinguished, and KS is distinguished.

_ Whether wi (WS) is differentiated by width WI and WS

Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.

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

Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.

Width differentiation: select this option if you want to make the comparison between halfwidth and fullwidth characters.