SQL Server converts database sorting rules

What is a sorting rule?

The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. Sorting Rules have the following features:

◆ Language

◆ Case sensitive

◆ Stress Differentiation

◆ Distinguish Kana

To understand the sorting rules currently used by the server, you can run the sp_helpsort system process in the SQL query analyzer.

SQL Server 7.0 does not support databases that use multiple sorting rules. Therefore, all databases created in SQL Server 7.0 use the default sorting rules. SQL Server 2000 supports multiple sorting rules. SQL Server 2000 databases can use other sorting rules except the default sorting rules. In addition, SQL Server 2000 supports the use of sorting rules (in addition to database-created sorting rules) columns.

Conditions for using the "use sorting rules" option in DTS

SQL Server 2000 can contain multiple databases or columns. They can use sorting rules other than the default sorting rules. Therefore, the data conversion Service (DTS) introduces a new option named "use sorting rules. The behavior of the "use sorting rules" option is determined by the type of conversion to be executed. If you want to transfer data between two instances of SQL Server 2000 and enable the "use sorting rules" option, the data will be converted from the source code page to the target code page. If the "use sorting rules" option is not enabled and the two instances of SQL Server 2000 share the same code page, data is transmitted directly. If the code page is different, data is converted from the source code page to the target code page. However, an error may occur during data transmission.

Note: If the sorting rule is used for the data itself and the column uses the COLLATE clause, the sorting rule is very important. Use the sorting rules option to determine whether to convert the code page when data is transferred from one sorting rule to another. The "use sorting rules" option does not affect whether the COLLATE attribute defined by the column is set. Therefore, if the source table contains columns with specific sorting rules created using the COLLATE clause, this sorting rule will be retained during data transmission, whether or not the "use sorting rules" option is enabled in the data conversion service wizard.

Conversion Method without changing database sorting rules

The following methods do not change the database sorting rules:

◆ Backup and Restoration: When restoring a database on a server, if the sorting rules used by the server are different from those used by the server for backup, the restored database is not converted to the new sorting rule. Database sorting rules remain unchanged.

◆ Detach and reattach: If you detach a database created using a sorting rule and re-attach the database to another server using other sorting rules, the database sorting rules will not change. The database sorting rules remain unchanged.

◆ Copy Database Wizard: The copy Database Wizard automates the process of separation and re-attachment. The database sorting rules remain unchanged.

Note: The copy Database Wizard is only available in SQL Server 2000.

Convert database sorting rules in SQL Server 7.0

To change the database sorting rules between two computers running SQL Server 7.0, you must create a user database and all objects on the target Server, and then use DTS or bcp utility to transmit data.

To transfer a database from a computer running SQL Server 7.0 to a computer running SQL Server 7.0 with another sorting rule, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all objects in the source database.

3. To export data from all tables in the database, use DTS or bcp utility.

4. Use the SQL Server Enterprise Manager or CREATE DATABASE statement to CREATE a new DATABASE on the target Server.

Note: When the create database statement is used, the DATABASE uses the same sorting rule as the computer running SQL Server 7.0.

5. Run the script created in step 2 using the SQL query analyzer to recreate all objects in the target database.

Note: This table and column use the same sorting rule as the computer running SQL Server 7.0.

6. Use DTS or bcp utility to import data to the target table.

Convert the database sorting rules in SQL Server 7.0 to those in SQL Server 2000.

To change the database sorting rules between SQL Server 7.0 and SQL Server 2000, you must, use appropriate sorting rules on the target server to create databases, columns, or both databases and columns. However, when transmitting data from SQL Server 7.0 to SQL Server 2000, you can use DTS to delete and recreate the object. Therefore, when performing this operation, you must enable the "use sorting rules" option in DTS.

Note: Do not use the DTS utility in SQL Server 7.0 to transmit all objects with the computer running SQL Server 2000. If data must be transmitted between SQL Server 7.0 and SQL Server 2000, you must use the DTS utility in SQL Server 2000.

To convert the database sorting rules in SQL Server 7.0 to those in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all objects in the source database.

3. If the column must use other sorting rules instead of the default sorting rules in the target database, make necessary changes to the sorting rules of the corresponding columns in the script.

4. Use appropriate sorting rules to create a new database on the target server.

5. Run the script created in step 2 on the target server using the SQL query analyzer to recreate all objects in the database.

Note: New tables and columns use the same sorting rules as databases unless you specify other sorting rules for these columns.

6. Use DTS or bcp utility to transmit data.

NOTE: If DTS is used, verify the following information:

◆ Make sure that the "use sorting rules" option is enabled when data is transmitted from SQL Server 7.0 to SQL Server 2000.

◆ Because an object has been created using an appropriate sorting rule on the target server, you must disable the "Create target object first" option.

Convert database sorting rules in SQL Server 2000

To convert database sorting rules in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Check whether the COLLATE clause is used for all columns.

3. Use appropriate sorting rules to create a new database on the target server.

4. If no column uses the COLLATE clause, use DTS to transmit data to the target server. To do this, enable the "use sorting rules" option for code page conversion and convert data to new sorting rules on the target database. If all columns use the COLLATE clause, follow these steps:

A. Generate scripts for all objects (excluding indexes, triggers, primary keys, foreign keys, default settings, and constraints. Additionally, make sure that the "write scripts only for features compatible with version 7.0" option is enabled to delete the COLLATE clause from the script.

Note: You can change the sorting rules when you use the "write scripts only for functions compatible with version 7.0" option. However, when a script is generated, no new SQL Server 2000 options (including user-defined functions, extended attributes, INSTEAD OF triggers, and view indexes) are taken into account ).

B. Run the script created in step a on the target database to create an object using the sorting rules of the target database.

C. Use DTS to transmit only data in the source database.

D. After data is successfully transferred, a script is generated for all constraints, foreign keys, primary keys, and indexes in the source database.

E. Run the script created in step d on the target database.

Change the sorting rules of the master database

If you want to change the sorting rules of the master database, you must recreate the master database. When the master database is rebuilt, a new master database is created. Therefore, before rebuilding the master database, consider the following:

◆ Make sure that a valid backup of the current master database is created. When the master database is rebuilt, the msdb database and the model database are also rebuilt. Therefore, msdb and model databases must be backed up before the master database is rebuilt. Msdb is a system database used to store SQL Server jobs, alarms, operators, and data transmission packages. The model database is the template database used to create a new database.

◆ Because a new master database will be created when the master database is rebuilt, you must re-enter the existing logon information after the master database is rebuilt. Therefore, you must export the logon information before recreating the master database, and then import the logon information after recreating the master database.

◆ Because msdb database is rebuilt when the master database is rebuilt, scripts must be generated for all jobs, alarms, and operators before the master database is rebuilt. In addition, make sure that all DTS Packages are moved.

◆ Because the model database will be rebuilt when the master database is rebuilt, you must write down or export all the previous changes to the model database or generate scripts for the master database before rebuilding the master database. After recreating the model database, apply all recorded changes again.

Modify the sorting rules of the master database in SQL Server 2000

Before changing the sorting rules of the master database, follow these steps:

1. After the master database is rebuilt, it will not automatically restore all databases created before the database is rebuilt. Therefore, all databases must be restored from the backup, or if the files are still on the disk, you can use the sp_attach_db system stored procedure to re-append the database. Make sure that all necessary information is reappended to the existing database before the master database is rebuilt.

2. When the master database is rebuilt, only the sorting rules of the master database, model database, and msdb database are changed. The sorting rules of the user database are not changed. To change the sorting rules of an existing user DATABASE or create a new DATABASE using the appropriate sorting rules, run the alter database command and then use DTS or bcp utility to transmit data to the new DATABASE.

Note: If you use the alter database command in SQL Server 2000 to change the DATABASE sorting rules, the column sorting rules in the table will not be changed automatically. To change the COLUMN sorting rules, run the alter table command and the alter column command. If DTS is used, you can use appropriate sorting rules to create tables and columns before transmitting data or using the "use sorting rules" option. If the DTS and tables used already contain appropriate sorting rules, make sure that the "Delete existing objects first" option is disabled before running the software package.

Modify the sorting rules of the master database in SQL Server 7.0

Before changing the sorting rules of the master database, follow these steps:

1. SQL Server 7.0 does not support database sorting rules other than default sorting rules. Therefore, before recreating the master database, export all data from the user database.

2. Generate scripts for all objects in the database.

3. Use appropriate sorting rules to recreate the master database.

4. Create a new database. Use the new default sorting rule to automatically create a new database.

5. Run the previously saved script to recreate the object and then import the previously exported data.