Solution to the conflict of SQL Server’s sorting rules

Time:2021-1-16

Question:
In the development of project database, sometimes the scripts we write have no problem when they are executed locally, but when they are deployed to the server, they are wrong when the scripts run. The Chinese and English error messages are as follows.
English: unable to solve the “SQL” problem in equal to operation_ Latin1_ General_ CP1_ CI_ As “and” Chinese_ PRC_ CI_ Collation conflict between as and as.
English:Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Chinese_PRC_CI_AS” in the equal to operation.

Root cause:
Operating system official account is based on WeChat public: SQL database programming, micro signal: zhang502219048. The problems encountered in the actual work are summarized as follows: if the machine is a Chinese operating system, and the server is an English operating system, and the script uses a temporary table and does not specify the collation rules of nvarchar or varchar text fields, and the table of the project database is connected to the join table connection. The default collation of project database is Chinese_ PRC_ CI_ This problem occurs when this text field is used in the on Association condition of as).

Solution:
Option 1(not recommended)
In the on condition field of the join join, specify a collation for the temporary table. This method is not recommended, because if there are 10 join temporary table statements, you have to write them 10 times, which is too cumbersome.

select *
from #t1 a
inner join t2 b 
    on b.name = a.name collate Chinese_PRC_CI_AS

Option 2(recommended)
In the nvarchar or varchar text field definition of the temporary table, add the specified collation rule, once and for all, there is no need to specify the collation rule in the on condition of join join.

create table #t1
(
    name nvarchar(50) collate Chinese_PRC_CI_AS
)

[please indicate the source of the blog: https://www.cnblogs.com/zhang502219048/p/12992611.html 】