SQL Server sorting rules

 

I. Introduction to sorting rules:

What is a sorting rule? "In Microsoft SQL Server,
The physical storage of strings is controlled by sorting rules. Sorting rules specify the bit mode and storage of each character
Rules Used for saving and comparing characters. "
Run the following statement in the query analyzer to obtain all the sorting rules supported by SQL Server.
Select * From: fn_helpcollations ()
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
The first half is the Unicode character set. The chinese_prc _ pointer sorts Unicode characters in simplified Chinese characters by pinyin.
Chinese_prc_stroke indicates sorting by strokes of 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-insensitive/case-sensitive)
_ Whether AI (AS) is stress-sensitive, AI is not differentiated, and as is (accent-insensitive/accent-sensitive)
_ KI (KS): whether Kana is distinguished. KKI is not distinguished. KS is distinguished (kanatype-insensitive/kanatype-sensitive)
_ Wi (WS): whether to differentiate width without wi; WS (width-insensitive/width-sensitive)
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.

Ii. Modify and view sorting rules:

------ Modify column sorting rules
ALTER TABLE TB
Alter column colname nvarchar (100) Collate chinese_prc_ci_as

------ Modify Database sorting rules
Alter database
Collate chinese_prc_cs_as

------ View the sorting rules of a table
Select collation from syscolumns
Where id = object_id (N 'yourtablename ')

V. Application of sorting rules:

SQL Server provides a large number of sorting rules for Windows and SQL Server, but its applications often
Ignored by developers. In fact, it is of great use in practice.

Example 1: sort the content of the table name column in pinyin order:

Create Table # T (ID int, name varchar (20 ))
Insert # T select 1, '中'
Union all select 2, 'status'
Union all select 3, 'people'
Union all select 4, 'A'

Select * from # T order by name collate chinese_prc_cs_as_ks_ws
Drop table # T
/* Result:
ID name
-------------------------------
4
2 countries
3 persons
Medium
*/

Example 2: sort the content of the table name column by the last name strokes:

Create Table # T (ID int, name varchar (20 ))

Insert # T select 1, '3'
Union all select 2, 'B'
Union all select 3, '2'
Union all select 4, '1'
Union all select 5, '10'
Select * from # T order by name collate chinese_prc_stroke_cs_as_ks_ws
Drop table # T
/* Result:
ID name
-------------------------------
4yi
2 B
3 II
5th
1 3
*/

 

 

Iii. troubleshooting of common problems:

1. "The sorting rule conflict for equal to operations cannot be resolved ."

Example 1:

Create Table # T1 (
Name varchar (20) Collate albanian_ci_ai_ws,
Value INT)

Create Table # T2 (
Name varchar (20) Collate chinese_prc_ci_ai_ws,
Value INT)

After the table is created, execute the connection query:

Select * from # T1 a inner join # T2 B on A. Name = B. Name

In this way, the error occurs:

Server: Message 446, level 16, status 9, Row 1
The sorting rule conflict of the equal to operation cannot be solved.
To eliminate this error, the simplest way is to specify its sorting rules when the table is connected.
No longer appears. The statement is written as follows:

Select *
From # T1 a inner join # T2 B
On a. Name = B. Name collate chinese_prc_ci_ai_ws

Example 2:

For example, when creating a table, consider using the following Transact-SQL statement:

Create Table testtab (

Id int,

Greekcol nvarchar (10) Collate greek_ci_as,

Latincol nvarchar (10) Collate latin1_general_cs_as

)

Insert testtab values (1, n 'A', N 'A ')

Go

This statement creates a table that contains the following two columns: A column uses a case-insensitive and accent-sensitive Greek sorting rule, the other column uses a general Latin1 sorting rule that is case sensitive and accent sensitive.

You can use a query to explicitly compare the two columns:

Select *

From testtab

Where greekcol = latincol

However, an error is returned for this query:

MSG 446, level 16, State 9, server V-MICHKA3, line 1

The sorting rule conflict of equal operations cannot be solved.

This error occurs because the server cannot use different sorting rules to compare two text segments. However, if you use the Collate keyword to explicitly create an expression that allows the two columns to be compatible, the query will be executed as follows:

Select *

From testtab

Where greekcol = latincol collate greek_ci_as

It should also be noted that although latincol usually has a case-sensitive sorting rule, the non-case sorting rule of the expression will overwrite it, therefore, the upper and lower case letters of "A" are considered equal.