在SQL SERVER中每个数据库都有自己的字符集设置(collation, 也叫排序设置), 那么在试图join或者union来自使用不同collation的数据库表时就会收到错误提示, 比如这样:
In SQL Server, each database may use different Collation setting than others, an error message as follows could pop up when trying to join or union two tables utilizing different Collations.
--table1使用Chinese_PRC_CI_AS
--table2使用SQL_Latin1_General_CP1_CI_AS
SELECT *
FROM tablel
LEFT JOIN table2
ON table1.keycolumn = table2.keycolumn
--------------------------------------------Msg 468, Level 16, State 9, Line 4
无法解决 equal to 运算中 "Chinese_PRC_CI_AS" 和 "SQL_Latin1_General_CP1_CI_AS" 之间的排序规则冲突。
解决的方法很简单, 在语句中需要转换的字段名后添加 collate语句就可以了.
It's quite easy to get resolved - inserting Collate clue statement right after the columns which need to be transferred to another collation
--根据你的需要,转换其中一个table的collation就可以了
--下面例子就是把使用中文字符集的字段转换成英文
SELECT *
FROM tablel
LEFT JOIN table2
ON table1.keycolumn COLLATE SQL_Latin1_General_CP1_CI_AS = table2.keycolumn
另外如果你使用的是union语句, 那么在select后面提及的column也需要使用collate语句, 比如这样
If you need to use UNION, Collate statement needs to be added for the column mentioned in SELECT statement
SELECT keycolumn COLLATE SQL_Latin1_General_CP1_CI_AS
FROM tablel
UNION ALL
SELECT keycolumn
FROM table2当然, 还可能会出现种情况, 你并不知道某个字段的collation怎么办? 用下图的语句, 配合sys.columns和sys.objects系统表就可以查出来了. 需要注意的是, 如果字段不是字符串型, 那么collation就会是null
Of course, sometimes you just have no idea what collation has been used for a specified column, what to do? The following screenshot suggests a way out by using two system tables altogether- sys.column and sys.objects. But one thing to be noted is that NULL will be returned for the NON-String columns, such as varchar and nvarchar
最后是用tsql的databasepropertyex来查某个数据库的collation设置, 当然更简单的办法是直接到数据库的属性页面去看 : )
One last thing is how to check collation of a database in TSQL, the following screenshot shows how to do it with function "databasepropertyex", but a more convenient way out is check it in database property page.
