Mixed use of simplified SQL Server Data

The databases used in the company are in simplified Chinese and Traditional Chinese versions. I am using SQLServer in this project in the Traditional Chinese version, but I need to use the data of another SQL server project in the Simplified Chinese version, I normally import a data table in the simplified Chinese SQLServer to the current traditional Chinese SQLServer, and the structure and data have been imported successfully. At the beginning, I didn't realize the problem. Next, I started to debug the SQL statement. In fact, it is a very simple two-table join data. The options are roughly as follows:

Select table1. *, table2 .*

From table1

Inner join table2 on table2.FK = table1.PK

The following error is always prompted in the query analyzer.

Server: Message 446, level 16, status 9, Row 1

The equal to action sequence cannot be parsed.

Later I searched, and then looked at the help of the T-SQL, only to know that it is because I imported the data table in the simplified Chinese SQL Server to the traditional Chinese data table is after, the data is imported together with the sorting method of the original data. As a result, the sorting method of the simplified data table is still simplified, so comparison cannot be performed, resulting in an error. The solution is to tell the query analyzer how to sort by name. Here we need to use the keyword COLLATE Coliate as explained in the SQL Server books online:

COLLATE

A clause can be applied to database definitions or column definitions to define sorting rules, or to string expressions to apply projection of sorting rules.

Syntax:

COLLATE <collation_name>

<Collation_name >::=

{Windows_collation_name} | {SQL _collation_name}

Parameters

Collation_name

Is the name of the sorting rule applied to expressions, column definitions, or database definitions. Collation_name can only be the specified Windows_collation_name or SQL _collation_name.

Windows_collation_name

Is the name of a Windows sorting rule. See Windows sorting rule name.

SQL _collation_name

Is the name of the SQL sorting rule. See SQL sorting rule name.

How can we know the name of the current sorting rule? In fact, This sorting rule name can be selected when we create a database instance, however, in general, we will default the original settings and will not change them. Therefore, if it is a simplified Chinese SQLServer, the sorting rules of Simplified Chinese will be used by default, if it is a traditional Chinese SQLServer, the sorting rules of traditional Chinese will be used by default. When viewing the database instance attributes, the bottom line of the General tab is the current sorting rule. By default, the name of the sorting rule in simplified Chinese is Chinese_PRC_CI_AS, while that in traditional Chinese is Chinese_Taiwan_Stroke_CI_AS, therefore, if the names of simplified and Traditional Chinese sorting rules are mixed, you only need to declare which sort rules you want to use for comparison. For example, for the preceding SQL statement, the following two methods can solve the problem of the error prompt:

SELECT Table1. *, Table2 .*

FROM Table1

Inner join Table2 ON Table2.FK = Table1.PK COLLATE Chinese_PRC_CI_AS

SELECT Table1. *, Table2 .*

FROM Table1

Inner join Table2 ON Table2.FK = Table1.PK COLLATE Chinese_Taiwan_Stroke_CI_AS

After talking about how to solve the problem caused by sorting rule conflicts for half a day, if you are still interested, I will share with you the concept of sorting rules in SQL Server online series, this saves you the trouble to go back:

Microsoft®SQL Server™2000 supports multiple sorting rules. Sort rules encode rules that control the correct use of languages such as Spanish or Polish) or the alphabet such as the Latin1_General character used by Western European languages.

Each SQL Server sorting rule specifies three attributes:

The sorting order of Unicode data types nchar, nvarchar, and ntext. The sort order defines the sort sequence of characters and the methods for comparing character values.

Used for sorting of non-Unicode character data types char, varchar, and text.

The code page used to store non-Unicode character data.

It indicates that the code page corresponding to Unicode data types nchar, nvarchar, and ntext cannot be specified. The double-byte mode for Unicode characters is defined by the Unicode Standard and cannot be changed.

You can specify the SQL Server 2000 sorting rule at any level. When installing an SQL Server 2000 instance, you can specify the default sorting rules for this instance. You can specify the default sorting rules for each database creation. If no sorting rule is specified, the default sorting rule of the database is the default sorting rule of the instance. You can specify sorting rules for character columns, variables, or parameters at any time. If no sorting rule is specified, these objects are created using the default sorting rule of the database.

If all users of the SQL Server instance use the same language, select a sorting rule that supports this language. For example, if all users speak French, select the French sorting rule.

If you are using SQL Server instances in multiple languages, select the sorting rules that best support multilingual requirements. For example, if users generally speak Western European languages, select Latin1_General sorting rules. When users in multiple languages are supported, Unicode data types nchar, nvarchar, and ntext are used for all character data. Unicode is designed to eliminate the difficulty of code page conversion for non-Unicode char, varchar, and text data types. Because the sorting rules define the sorting order used for comparison operations and the sorting of Unicode characters, when Unicode data types are used to implement all columns, the sorting rules will still be different. Even if the Unicode data type is used to store character data, you should select to support the sorting rules of most users to prevent non-Unicode data types from being used to implement columns or variables.

SQL Server sorting rules define how the database engine stores and operates characters and Unicode data. However, when data is moved into the application, the collation and comparison in the application will be controlled by the Windows region selected on the computer. The character data sorting rules used by the application are one of the projects controlled by the Windows region settings. The region settings also define other projects, such as numbers, times, dates, and currency formats. For Microsoft Windows NT®4.0 Microsoft Windows®98 and Microsoft Windows 95, you can use the "region Settings" application in the control panel to specify Windows region settings. For Microsoft Windows 2000, you can use the "region options" Application in "Control Panel" to specify the region settings. For more information about Windows region settings, see the Microsoft Web site MSDN®On the page, Developing International Software for Windows 95 and Windows NT 4.0.

Multiple sorting rules can use the same code page for non-Unicode data. For example, code page 1251 defines the Spanish character set. This code page is used for multiple sorting rules, such as Cyrillic_General, Ukrainian, and Macedonian. Although these sorting rules all use the same bitset to represent non-Unicode character data, the sorting and comparison rules applied when processing dictionary definitions are slightly different, the dictionary defines the correct sequence of Characters Related to sorting rules in the language or alphabet.

Because the SQL Server 2000 sorting rules control the Unicode and non-Unicode sorting order, you will not encounter problems caused by specifying different sorting rules for Unicode and non-Unicode data. In earlier versions of SQL Server, the code page number, character sorting order, and Unicode sorting rules are specified respectively. Earlier versions of SQL Server also support sorting order of different numbers for each code page, and provide sorting order that is not set in Windows for some code pages. In SQL Server 7.0, you can also specify Unicode sorting orders other than those selected for non-Unicode data. This results in different results returned for sorting and comparison operations when Unicode data is used relative to non-Unicode data.

1. Notes for using temporary tables in a T-SQL
2. SQL Server database management common SQL and T-SQL statements (1)
3. Interview SQL Server developers with T-SQL operations (1)
4. T-SQL in SQL Server 2005
5. T-SQL
6. Dangerous judgment on Microsoft SQLServer password management