How to change MSSQL database sorting rules

1. sp_helpsort
Select serverproperty ('colation ')
View your sorting rules.
However, this should be related to the character set.
2. Change server sorting rules
Changing the default sorting rules for SQL Server 2005 instances may be complicated by the following steps:
Make sure that you have all the information or scripts required to recreate the user database and all objects in the database.
Use tools (such as large-capacity replication) to export all data.
Delete all user databases.
Regenerate the master database that specifies the new sorting rule in the SQLCOLLATION attribute of the setup command. For example:
Start/wait setup.exe/qb INSTANCENAME = mssqlserver reinstall = SQL _Engine REBUILDDATABASE = 1 SAPWD = test SQLCOLLATION = SQL _Latin1_General_CP1_CI_AI
For details about how to regenerate the master database of SQL Server 2005, see how to regenerate the Master database of SQL Server.
Create all databases and all objects in these databases.
Import all data.
Note::
You can specify a default sorting rule for each created database without changing the default sorting rule for the SQL Server 2005 instance.
3. Set and Change Database sorting rules
When creating a new database, you can use one of the following to specify the sorting rules:
The COLLATE clause of the create database statement.
SQL Server Management Studio.
The Database. Collation attribute in the SQL management object (SMO.
If no sorting rule is specified, the server sorting rule is used.
You can use the COLLATE clause of the alter database statement to change the sorting rules for any new objects created in the user DATABASE. Using this statement, you cannot change the sorting rules of columns in any existing user-defined table. You can use the alter table collate clause to change the sorting rules of these columns.
When changing database sorting rules, you must change the following:
The default sorting rule of the database. This new default sorting rule will be applied to all columns created in the database, user-defined data types, variables, and parameters. When parsing the object identifier specified in the SQL statement based on the object defined in the database, the new default sorting rule is also used.
Change any char, varchar, text, nchar, nvarchar, or ntext columns in the system table to use the new sorting rule.
Change all existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values of stored procedures and user-defined functions to use new sorting rules.
Change the system data types of char, varchar, text, nchar, nvarchar, or ntext, and all user-defined data types based on these system data types to use the new default sorting rules.
SQL code:
1. Modify the character set of the database:
Copy codeThe Code is as follows:
Alter database dbname collate Chinese_PRC_CI_AS

2.
-- 1. Specify sorting rules for the database
Copy codeThe Code is as follows:
Create database db COLLATE Chinese_PRC_CI_AS
GO
Alter database db COLLATE Chinese_PRC_BIN
GO

/* = */
-- 2. Specify sorting rules for columns in the table
Copy codeThe Code is as follows:
Create table tb (
Col1 varchar (10 ),
Col2 varchar (10) COLLATE Chinese_PRC_CI_AS)
GO
Alter table tb ADD col3 varchar (10) COLLATE Chinese_PRC_BIN
GO
Alter table tb alter column col2 varchar (10) COLLATE Chinese_PRC_BIN
GO

/* = */
-- 3. Apply sorting rules for character variables and parameters
Copy codeThe Code is as follows:
DECLARE @ a varchar (10), @ B varchar (10)
SELECT @ a = 'A', @ B = 'A'
-- Use the sorting rule Chinese_PRC_CI_AS
Select case when @ a COLLATE Chinese_PRC_CI_AS = @ B THEN '@ a = @ B 'else' @ a <> @ B' END
-- Result: @ a = @ B
-- Use the sorting rule Chinese_PRC_BIN
Select case when @ a COLLATE Chinese_PRC_BIN = @ B THEN '@ a = @ B 'else' @ a <> @ B' END
-- Result: @ a <> @ B

3.
Table
Copy codeThe Code is as follows:
Alter table tb
Alter column colname nvarchar (100) COLLATE Chinese_PRC_CI_AS
-- Case insensitive
Alter table tb
Alter column colname nvarchar (100) COLLATE Chinese_PRC_CS_AS
-- Case sensitive

Database
Copy codeThe Code is as follows:
Alter database database
COLLATE Chinese_PRC_CS_AS
-- Case sensitive
Alter database database COLLATE Chinese_PRC_CI_AS -- case insensitive

Method 1. Select case sensitive when installing SQL
Or recreate mastar after installation.
C: \ Program Files \ Microsoft SQL Server \ 80 \ Tools \ Binn \ rebuildm.exe
Method 2. SQL server 8.0 or above is supported, and 7.0 or below is not supported
Alter database COLLATE Chinese_PRC_CS_AS
Modify the sorting rule to a case-sensitive sorting rule.
If only one table is modified, use the alter table statement.
If you modify the default sorting rules of a database, use the alter datebase statement.
If you modify the sequence rules of the entire server, use rebuildm.exe to recreate the master database.
-- Specify the sorting rule.
-- Example
Copy codeThe Code is as follows:
Select replace ('abacb' collate Chinese_PRC_CS_AS_WS, 'B', 'test ')

-- If you require table support, you can specify the sorting rules when creating the table so that replace does not need to write the sorting rules.
-- Example
Copy codeThe Code is as follows:
Create table tb (a varchar (20) collate Chinese_PRC_CS_AS_WS)
Insert tb values ('abac ')
Select replace (a, 'A', 'test') from tb
Drop table tb

Specify the sorting rule.
The Windows sorting rule name is specified in the COLLATE clause. The Windows sorting rule name consists of the sorting rule indicator and comparison style.
Syntax
Copy codeThe Code is as follows:
<Windows_collation_name >::=
CollationDesignator _ <ComparisonStyle>
<ComparisonStyle >::=
Casesensitivity_accentsensiti.pdf
[_ KanatypeSensitive [_ WidthSensitive]
| _ BIN

Parameters
CollationDesignator
Specifies the basic sorting rules used by Windows sorting rules. Basic sorting rules include:
Specifies the alphabet or language in which the sorting rules are applied when sorting by dictionary is specified.
The code page used to store non-Unicode character data.
For example, Latin1_General or French, both use the code page 1252 or Turkish, and use the code page 1254.
Casesensiti.pdf
The CI parameter is case insensitive and the CS parameter is case sensitive.
Accentsensiti.pdf
AI specifies no accent, and AS specifies the accent.
KanatypeSensitive
Omitted specifies case-insensitive, and KS specifies a Kana type.
Widthsensiti.pdf
Omitted specifies case-insensitive, and WS specifies case-sensitive.
BIN
Specify the binary sorting order.
If you only distinguish the current query, do not change it like this to avoid regret. The query is as follows:
Select * from
/*
A_nam a_add
--------------------
1 aa
1 bb
2 cc
2 vv
2 kk
3 dd
3 ee
4 dd
5 ee
6 yy
6 yy
(11 row (s) affected)
*/
Now we can query a_add = 'A', 'A', and so on!
Example 1:
Copy codeThe Code is as follows:
Select * from
Where a_add collate Chinese_PRC_CS_AS_WS = 'A'
/*
A_nam a_add
--------------------
1 aa
(1 row (s) affected)
*/

Example 2:
Copy codeThe Code is as follows:
Select * from
Where a_add collate Chinese_PRC_CS_AS_WS = 'A'
/*
A_nam a_add
--------------------
(0 row (s) affected)
*/

Example 3. The above cannot be remembered, so we use the most stupid method to convert it to ascii
Copy codeThe Code is as follows:
Select * from
Where
Ascii (substring (a_add, 1, 1) = ascii (substring ('A', 1, 1 ))
And
Ascii (substring (a_add, 2, 1) = ascii (substring ('A', 2, 1 ))
/*
A_nam a_add
--------------------
(0 row (s) affected)
*/

Example 4: Any version can
Copy codeThe Code is as follows:
Select * from
Where cast (a_add as varbinary (10) = cast ('A' as varbinary (10 ))