代码之家 › 专栏 › 技术社区 › ErikHeemskerk

SQL Server 2008:是否在唯一索引中忽略排序规则?

unique-index collation sql-server-2008 sql-server

0

ErikHeemskerk · 技术社区 · 16 年前

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

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拉丁1_general_cp1_ci_as。当我运行下面的T-SQL时,它会打印“不等于”:

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

然后,如果我尝试以下insert语句:

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'.

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

我顺便检查了表、列和索引排序规则,它们都等于数据库排序规则。

1 回复 | 直到 16 年前

1

8kb 16 年前

试试这个:

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。