MSSQL sorting Rule Summary

What is a sort rule? The sorting rules specify the rules for sorting and comparing string data based on the criteria set for specific languages and regions. SQLServer supports storing objects with different sorting rules in a single database. MSDN explanation: In MicrosoftSQLServer, the physical storage of strings is controlled by sorting rules. Sorting rules

What is a sort rule? The sorting rules specify the rules for sorting and comparing string data based on the criteria set for specific languages and regions. SQL Server supports storing objects with different sorting rules in a single database. MSDN explanation: in Microsoft SQL Server, the physical storage of strings is controlled by sorting rules. Sorting rules

Sorting rule terminology

 

What is a sorting rule? The sorting rules specify the rules for sorting and comparing string data based on the criteria set for specific languages and regions. SQL Server supports storing objects with different sorting rules in a single database. MSDN explanation: in Microsoft SQL Server, the physical storage of strings is controlled by sorting rules. The sorting rule specifies the bit mode of each character and the rules used to store and compare characters.

When a Transact-SQL statement runs in different database contexts with different sorting rules, the running results may be different. If possible, use standardized sorting rules for your organization. In this way, you do not need to explicitly specify the sorting rules for each character or Unicode expression. If you must use objects with different sorting rules and code pages, encode the query to consider the priority rules of the sorting rules.

The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. The ordering rules feature language-sensitive, case-sensitive, stress-sensitive, and Kana-specific. As follows:

The first half of Chinese_PRC_CI_AS indicates the UNICODE character set. The Chinese_PRC _ pointer sorts UNICODE characters in simplified Chinese characters. CI indicates case-insensitive and AS indicates accent-sensitive.

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

_ BIN indicates the use of backward compatible binary sorting order.

_ BIN2 specifies the binary sorting order in which the bitwise comparison semantics introduced in SQL Server 2005 is used.

_ Stroke sort by strokes

_ 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)

_ Whether WI (WS) is fully half-width, WI is not differentiated, and WS is differentiated (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.

You can use the following system function to view the sorting rules supported by the database:

Select * from: fn_helpcollations ();

 

Sorting rule type

SQL Server provides two sorting rules: Windows and SQL Server. For more information, see MSDN.

 

View server sorting rules

Select serverproperty (n' Collation ')

 

View database sorting rules

SQL 1:,) SQL 2:, collation_name sys. databases;

 

 

View column sorting rules

SQL 1:

SELECT c. object_id, c. name, t. name, c. collation_nameFROM sys. columns cLEFT JOIN sys. types t on t. system_type_id = c. system_type_id ();

 

Modify server sorting rules

 

The reasons for modifying server sorting rules vary widely. In most cases, the server sorting rules are ignored during installation and are not properly planned beforehand, the problem will not be found during the test until the database is restored or migrated to the new server.

When you modify the server sorting rules to regenerate the master, model, msdb, and tempdb System databases, these system databases will be deleted and re-created at the default location. If a new sorting rule is specified in the regenerate statement, This sorting rule is used to create a system database. All modifications made to these databases will be lost. For example, account information, jobs, and linked servers.

MSDN about setting and changing server sorting rules

Sqls 2005

Start/wait setup.exe/qb INSTANCENAME = mssqlserver reinstall = SQL _Engine REBUILDDATABASE = 1 SAPWD = test SQLCOLLATION = SQL _Latin1_General_CP1_CI_AI

Sqls 2008

Setup/QUIET/ACTION = REBUILDDATABASE/INSTANCENAME = InstanceName/SQLSYSADMINACCOUNTS = accounts/[SAPWD = StrongPassword]/SQLCOLLATION = CollationName

The modification of server sorting rules seems very simple. In fact, if you are not careful, you will never be able to get rid of it. I encountered a neglected step in practice, and it turned out to be a cold sweat. The steps are as follows:

Step 1: First back up the database (including the system database and user database ). Do not miss the system database backup. If you have a slave node, you will not be affected. Otherwise, you will have to worry about every step of the operation.

Step 2: record some server configuration values you have modified in the document. For example, in SQL SERVER 2008, you may enable backup compression default. After awe enabled is enabled for a 32-bit database, you need to apply and configure these values again. Avoid omissions and cause database performance problems.

Step 3: record the path of the data file and log file of the system database. Rebuild the system database to install all the system databases to its original location. If you have not moved the system database file or log file, this can be ignored, for many reasons such as I/O performance, these System database files and log files may be moved.

Step 4: Use the document to sort out the login name (logins) and related passwords. Because the server sorting rules are modified, the system database master, msdb, tempd, and other information will be rebuilt. the login name and other information will not exist, and you need to re-create and configure them.

Step 5: generate an SQL script for an existing job. After modifying the server sorting rules, you can recreate and deploy a job. The same is true.

Step 6: Generate sorting rules for existing linked servers, so that you can easily modify the server sorting rules and recreate and deploy the linked servers. The same is true.

Step 7: sort out the database mail configuration file and the created account, so that you can easily modify the server sorting rules and reconfigure them.

Step 8: If you have configured release-subscription on the instance, you also need to sort out the relevant scripts and documents.

Step 9: Separate the databases created by users (this Step is not necessary ).

Step 10: Modify server sorting rules

Step 11: Add a database separated by Step 9.

Step 12: Solve isolated accounts, configure jobs, and link servers .....

Of course, the seemingly simple operation process, in fact, in different environments, you will always encounter some unexpected situations

Example 1:

D: \ Software tools \ SQL SERVER 2008> Setup/QUIET/ACTION = REBUILDDATABASE/INSTANCENAME = M

SSQLSERVER/SQLSYSADMINACCOUNTS = sa/SAPWD = 123456/SQLCOLLATION = SQL _Latin1_General_CP1_CI_AS

Microsoft (R) SQL Server 2008

The following error occurred:

The specified sa password does not meet the strong password requirements. For more information about strong password requirements, see "database engine configuration-account settings" in the installer help or SQL Server 2008 books online ".

Error result:-2068578304.

Result facility code: 1204

Result error code: 0

Please review the summary.txt log for further details

This requires you to change the sa password to meet the strong password requirements.

Example 2: accidentally adding spaces in the/SAPWD, the following error is returned.

 

Example 3: When a database is attached, the sa account is not used. Instead, the system uses the windows identity created by sa to log on to the verification account and attaches the database. The following error is returned. Use the sa account to attach the database. The problem is solved.

 

In addition, I have encountered two exceptions before, and it is very difficult to reproduce them all at once. I will try again later.