Блог

 CREATE TABLE [dbo].[MessageTest]
(
    [ID] [uniqueidentifier] NOT NULL,
    [GatewayMessageId] [nvarchar](255) NULL,
    [GatewayOriginalMessageId] [nvarchar](255) NULL,

    CONSTRAINT [MessageTest_PK] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO

CREATE UNIQUE INDEX Message_GatewayIDs_IDX 
ON [dbo].[MessageTest] (GatewayMessageId, GatewayOriginalMessageId) 
WHERE GatewayMessageId IS NOT NULL
  AND GatewayOriginalMessageId IS NOT NULL

-- Should work fine
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)
INSERT INTO [dbo].[MessageTest] VALUES (NEWID(), NULL, NULL)

-- Should work
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)

-- Should not work???
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)

-- Should work
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', '257DC188-D687-4071-8A1A-04AAD50411E4')

-- Does not work as expected
INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', '257DC188-D687-4071-8A1A-04AAD50411E4')
  

Почему уникальный отфильтрованный индекс не предотвращает выполнение инструкции insert при наличии null со второй попытки?

 INSERT INTO [dbo].[MessageTest] 
VALUES (NEWID(), '257DC188-D687-4071-8A1A-04AAD50411E4', NULL)
  

Чего мне здесь не хватает?

Я знаю, если я изменю индекс на

 CREATE UNIQUE INDEX Message_GatewayIDs_IDX 
ON [dbo].[MessageTest] (GatewayMessageId, GatewayOriginalMessageId) 
WHERE GatewayMessageId IS NOT NULL
  

Тогда это работает так, как я ожидал, что предыдущее предоставление GatewayMessageId всегда заполняется первым.

Это ошибка?

Ограничение уникальности индекса означает, что значения в индексе должны быть уникальными.

Ваш фильтр по индексу

 where GatewayMessageId IS NOT NULL
    AND GatewayOriginalMessageId IS NOT NULL
  

(другими словами — оба GatewayMessageId и GatewayOriginalMessageId должны иметь значения).

Однако, поскольку вы вставляете строку в таблицу с GatewayOriginalMessageId = NULL помощью, она не включается в отфильтрованный индекс и, следовательно, не подпадает под ограничение уникальности в этом индексе.

При создании индекса можно использовать несколько настроек:

• уникальный — гарантирует отсутствие дублированных значений и, как и в операторах set (UNION, EXCEPT, INTERSECT), здесь NULL = NULL , что означает, что вы можете иметь только одно значение, поскольку два нуля находятся как дубликаты друг друга;
• кластеризованный / некластеризованный — логический порядок значений ключа определяет физический порядок соответствующих строк в таблице, если индекс кластеризован
• ВКЛЮЧИТЬ — позволяет указать неключевые столбцы, которые будут добавлены на конечный уровень некластеризованного индекса (для уменьшения поиска ключей)
• ГДЕ — указание, какие строки включать в индекс

Итак, WHERE предложение ограничивает строки, включенные в индекс. Оттуда он может иметь значительно меньший размер, чем index без предложения filtering. А меньший размер означает, что считывается меньше данных (меньше ввода-вывода) и потенциально более высокая производительность. Требование к запросу для его использования — содержать то же WHERE предложение, что и индекс.

И из приведенного выше вашего кода:

 WHERE GatewayMessageId IS NOT NULL
  AND GatewayOriginalMessageId IS NOT NULL
  

фильтрует, какие строки должны быть частью индекса, а не обеспечивает целостность данных.

Кроме того, объявление PRIMARY KEY UNIQUE ограничения or приводит к тому, что SQL Server автоматически создает индекс.

Уникальный индекс может быть создан без сопоставления с ограничением, но ограничение (либо первичный ключ, либо уникальный) не может существовать без уникального индекса.

И, как мы уже говорили, UNIQUE ограничение позволяет вставлять одно null значение, поэтому, если вы не хотите иметь NULL значения, вам нужно объявить столбец как таковой:

 ALTER TABLE [...]
ALTER COLUMN [...] [type] NOT NULL;
  

и если в таблице уже NULL есть строки, необходимо сначала заполнить их — одним из способов является создание a DEFAULT CONSTRAINT с WITH VALUES опцией. Но обратите внимание, что это заблокирует вашу таблицу. Другой способ — сначала выполнить пакетное обновление.