症状

在 SQL Server 数据库中下列情况属实,则有一个表︰

  • 该表包含 Unicode 列。例如,表中有nchar(5)列。

  • Unicode 列的排序规则是"Latin1_General_BIN"。

  • 同一个 Unicode 列是索引的一部分。

不过,针对此表运行的 T SQL 语句可能会返回不正确的结果。满足以下条件时,会发生此问题:

  • T-SQL 语句包含"IN"或"或者"同一 Unicode 列定义的子句。

  • T-SQL 语句包含"逐份打印"而转换为另一个二进制排序规则的 Unicode 列的类型。


示例查询︰

CREATE TABLE [dbo].[Table_1]( [Col1] [smallint] NOT NULL,
[Col2] [nchar](5) ,
[Col3] [nchar](5) COLLATE Latin1_General_BIN NOT NULL, -- Col3 , a Unicode Column with “Latin1_General_BIN” collation
CONSTRAINT [PK__Table_1] PRIMARY KEY CLUSTERED -- Primary Key on all the 3 columns
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


select * from Table_1 where Col1 = 1 and Col2 = '1' and Col3 collate Chinese_PRC_BIN IN (N'1' ,N'2') -- This statement using “IN” and “collate” might give incorrect results.
go


select * from Table_1 where Col1 = 1 and Col2 = '1' and (Col3 collate Chinese_PRC_BIN = N'1' or Col3 collate Chinese_PRC_BIN = N'2') -- This statement using “OR” and “collate” might give incorrect results.
go

状态

Microsoft 已经确认这是"适用于"一节中列出的 Microsoft 产品中的一个问题。我们目前正在对此问题的修复程序。其他信息可用时,将更新本文。

解决方法

若要变通解决此问题,请确保 Unicode 列 (第 3 列的"症状"部分中的示例查询中) 符合下列条件之一︰

  • 为数据类型char(5)或nvarchar(5)。

  • 定义使用相同的排序规则的"Chinese_PROC_BIN"的逐份打印需要 (请注意,"Chinese_PROC_BIN"只是一个示例; 其他二进制排序规则同样适用)。

  • 是排序规则之外"Latin1_General_BIN"。

  • 在 CI 排序规则逐份打印。例如︰ 对照 Chinese_PRC_90_CI_AI IN ('1' N,N '2')。

  • 一个常数,用于匹配的列长度进行比较。例如,对照 Chinese_PRC_BIN IN ('1' N,N '2')。

  • 不是索引或表扫描,会强制使用 FORCESCAN 表提示。

  • 如 RTRIM 和 LTRIM 函数用于强制执行表扫描。

详细信息

要重现此问题,请运行下面的脚本︰
CREATE DATABASE Test_DB GO

use Test_DB
go


CREATE TABLE [dbo].[Table_1](
[Col1] [smallint] NOT NULL,
[Col2] [nchar](5) ,
[Col3] [nchar](5) COLLATE Latin1_General_BIN NOT NULL, -- Col3 , a Unicode Column with “Latin1_General_BIN” collation

CONSTRAINT [PK__Table_1] PRIMARY KEY CLUSTERED -- Primary Key on all the 3 columns
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- Populate the table with a sample script as below

declare @x as int
declare @y as int

set @x=1
set @y=1

while (@x<=2)
begin
while (@y<=1000)
begin
insert into Table_1 values (@x,@y,@y)
set @y=@y+1
end
set @x=@x +1
end
go



select * from Table_1 where Col1 = 1 and Col2 = '1' and Col3 collate Chinese_PRC_BIN = N'1' -- Expected output of one row.
go


select * from Table_1 where Col1 = 1 and Col2 = '1' and Col3 collate Chinese_PRC_BIN in (N'1' ,N'2') -- No rows returned when output for Col3= N'1' is expected.
go


select * from Table_1 where Col1 = 1 and Col2 = '1' and (Col3 collate Chinese_PRC_BIN = N'1' or Col3 collate Chinese_PRC_BIN = N'2') -- No rows returned when output for Col3= N'1' is expected.
go

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×