Change sorting rules

1. sp_helpsort select serverproperty ('colation') to view your sorting rules. however, this should be related to the character set. 2. changing Server sorting rules to change the default sorting rules of SQL Server 2005 instances may be complicated, including 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, copy the code start/wait setup.exe/qb INSTANCENAME = mssqlserver reinstall = SQL _Engine REBUILDDATABASE = 1 SAPWD = test SQLCOLLATION = SQL _Latin1_General_CP1_CI_AI to regenerate the master database, see how to regenerate the Master database of SQL Server 2005. 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. When setting and changing DATABASE sorting rules to CREATE a new DATABASE, you can use one of the following to specify the sorting rules: COLLATE clause of the create database statement. The Database. Collation attribute in the SQL Server Management Studio. 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: default database sorting rules, this new default sorting rule applies to all columns subsequently 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 to alter database dbname collate Chinese_PRC_CI_AS2. -- 1. create database db COLLATE Chinese_PRC_CI_ASGOALTER DATABASE db COLLATE Chinese_PRC_BINGO/* ================ =============== */-- 2. specify the sorting rule create table tb (col1 varchar (10), col2 varchar (10) COLLATE Chinese_PRC_CI_AS) goalter table tb ADD col3 varchar (10) COLLATE Partition TABLE tb ALTER COLUMN Col2 varchar (10) COLLATE Chinese_PRC_BINGO/* ====================================== */-- 3. apply the sorting rules DECLARE @ a varchar (10), @ B varchar (10) SELECT @ a = 'A' for character variables and parameters ', @ B = 'A' -- use the sorting rule Chinese_PRC_CI_ASSELECT 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_BINSELECT case when @ a COLLATE Chinese_PRC_BIN = @ B THEN '@ a = @ B 'else' @ a <> @ B' END -- result: @ a <> @ b3. ALTER TA BLE 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 alter database database collate Chinese_PRC_CS_AS -- Case Sensitive ALTER database database collate Chinese_PRC_CI_AS -- Case Insensitive method 1. when installing SQL, select case sensitive or recreate mastar after installation, and select C: \ Program Files \ Microsoft SQL Server \ 80 \ Tools \ Binn \ rebuildm.exe method 2. SQL server 8.0 or more Only available in version 7.0 and earlier versions. alter database COLLATE Chinese_PRC_CS_AS is not supported to modify the sorting rule. If you change it to a case-sensitive sorting rule, if you use the alter table statement to modify the default sorting rules of a database, use the alter datebase statement "modify" to reconstruct the master database -- specify the sorting rules -- Example 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, in this way, replace does not need to write sorting rules. -- create table tb (a varchar (20) collate Chinese_PRC_CS_AS_WS) insert tb values ('abac') select re Place (a, 'A', 'test') from tbdrop table tb you can specify the name of a Windows sorting rule in the COLLATE clause. The Windows sorting rule name consists of the sorting rule indicator and comparison style. Syntax <Windows_collation_name >::= CollationDesignator _ <ComparisonStyle> :: = casesensitivity_accentsensititive [_ KanatypeSensitive [_ WidthSensitive] | _ BIN parameter CollationDesignator specifies the basic sorting rules used by Windows sorting rules. Basic sorting rules include the code page that uses the alphabet or language used to store non-Unicode character data when a dictionary-based sorting rule is specified. For example, Latin1_General or French, both use the code page 1252 or Turkish, and use the code page 1254. CaseSensitivityCI specifies case-insensitive, while CS specifies case-sensitive. AccentSensitivityAI specifies no accent, and AS specifies the accent. KanatypeSensitiveOmitted specifies that the name is case insensitive, and KS specifies that the name is a Kana type. WidthSensitivityOmitted is case insensitive and WS is case sensitive. BIN specifies 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/* a_nam a_add ---------- 1 aa1 bb2 cc2 vv2 kk3 dd3 ee4 dd5 ee6 yy6 yy (11 row (s) affected) */now we can query a_add = 'A', 'A', and so on! Example 1: select * from a where a_add collate Chinese_PRC_CS_AS_WS = 'A'/* a_nam a_add ---------- 1 aa (1 row (s) affected) */Example 2: select * from a where a_add collate Chinese_PRC_CS_AS_WS = 'A'/* a_nam a_add ---------- -------- (0 row (s) affected) */method 3. the above cannot be remembered, so we use the most stupid method to convert it to asciiselect * from awhere ascii (substring (a_add,) = ascii (substring ('A )) and ascii (substring (a_add,) = ascii (substring ('A',)/* a_nam a_add ---------- (0 row (s) affected) */method 3: Any version can select * from a where cast (a_add as varbinary (10) = cast ('A' as varbinary (10 ))