sql-server - 排序规则与临时表冲突

Question

tempdb 的排序规则为 Latin1_General_100_CI_AI。数据库的排序规则也是Latin1_General_100_CI_AI。然而下面的SQL语句:

SELECT *
FROM ##CitiesMapping AS cm
INNER JOIN Cities ON cm.CityName=Cities.Name

返回:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AI" in the equal to operation.

服务器默认排序规则也是Latin1_General_100_CI_AI

Answer 1

单个列的排序规则可能设置不同。 Stuart 的询问会告诉你这一点。如果它们是不同的排序规则，您可以指定比较两侧使用的排序规则，如下所示:

SELECT *
  FROM ##CitiesMapping AS cm
 INNER JOIN Cities 
    ON cm.CityName COLLATE DATABASE_DEFAULT = Cities.Name COLLATE DATABASE_DEFAULT;

希望这对您有所帮助。