当心T-SQL中COLLATE的陷阱

在SQLServer中用户可以在Server，数据库，表字段（列）和表达式级别定义字符集的类型。如果下一级没有定义字符集类型，则SQL Server会默认为上一级字符集类型。

 

举例如下：

 

 

declare

 

 

@Users

table

 

(

 

 

UserName nvarchar ( 255 ) collate SQL_Latin1_General_CP1_CI_AS not

null)

 

 

declare

 

 

@Users2

table

 

(

 

 

UserName nvarchar ( 255 ) collate SQL_Latin1_General_CP1_CS_AS not

null)

 

 

select

 

 

u1 .* from @Users u1 inner join @Users2 u2 on u1 . UserName = u2 . UserName

 

 

 

 

 

执行上述语句时会出现如下错误：

Msg 468, Level 16, State 9, Line 8

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

 

出现这种错误， 我们需要修改语句如下：

 

 

 

 

 

 

select

 

 

u1 .*

from

 

 

@Users u1

 

 

inner join @Users2 u2 on u1 . UserName = u2 . UserName collate

SQL_Latin1_General_CP1_CI_AS

 

上例中我们在两个表变量中都声明了collate，所以问题比较明显。如果我们只定义其中一个，而另外一个采用系统默认，此时问题可能就会很难发现。

 

举例如下：

 

 

declare

 

 

@Users

table

 

(

 

 

UserName nvarchar(255) collate SQL_Latin1_General_CP1_CI_AS not

 

null)

 

 

declare

 

 

@Users2

 

table

 

(

 

 

UserName nvarchar(255) not null)

 

 

 

select

 

 

u1.* from @Users u1 inner join @Users2 u2 on u1.UserName=u2.UserName

 

如果测试时SQL Server默认的collate正好和表变量@Users中的相同，则问题不会再现，而产品发布库中的collate如果和@Users中的UserName列不同，问题就会出现了。

因此，在T-SQL开发中，如果强制定义了collate，则最好将collate同时指定给所有使用该列的表达式以避免字符集比较时兼容性的问题。