I've created a compound unique index on my table:
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]
The collation for the entire database is set to SQL_Latin1_General_CP1_CI_AS. When I run the following T-SQL, it prints 'Does not equal':
IF 'm3/h' = 'm³/h'
PRINT 'Equals'
ELSE
PRINT 'Does not equal'
Then, if I try the following insert statement:
INSERT INTO [SearchIndexWord] (Word, CatalogID) VALUES ('m3/h', 1), ('m³/h', 1)
I get the following error message:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.SearchIndexWord' with unique index 'IX_SearchIndexWord'.
Why is this? I couldn't find it in the docs, but I assume the condition of two keys being duplicate is examined using the configured collation.
I've checked the table, column and index collation by the way, and they're all equal to the database collation.