SQL Server 2008：唯一索引中忽略排序规则？

Question

我在表上创建了一个复合唯一索引：

CREATE TABLE [dbo].[SearchIndexWord](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CatalogID] [int] NOT NULL,
    [Word] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_SearchIndexWord] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) 
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) 
ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_SearchIndexWord] ON [dbo].[SearchIndexWord] 
(
    [Word] ASC,
    [CatalogID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]

整个数据库的排序规则设置为 SQL_Latin1_General_CP1_CI_AS。当我运行以下 T-SQL 时，它会打印“不等于”：

IF 'm3/h' = 'm³/h'
    PRINT 'Equals'
ELSE
    PRINT 'Does not equal'

然后，如果我尝试以下插入语句：

INSERT INTO [SearchIndexWord] (Word, CatalogID) VALUES ('m3/h', 1), ('m³/h', 1)

我收到以下错误消息：

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.SearchIndexWord' with unique index 'IX_SearchIndexWord'.

这是为什么？我在文档中找不到它，但我假设使用配置的排序规则检查两个键重复的条件。

我顺便检查了表、列和索引排序规则，它们都与数据库排序规则相同。

Answer 1

试试这个：

IF CAST('m3/h' AS NVARCHAR(100)) = CAST('m³/h' AS NVARCHAR(100))
    PRINT 'Equals' 
ELSE 
    PRINT 'Does not equal' 

对我来说，这会返回 'Equals' 这解释了为什么您会收到重复键行错误。

我怀疑代码 IF 'm3/h' = 'm³/h' 中的值被创建为 VARCHAR。