無法使用 IGNORE_DUP_KEY = ON 創建 UNIQUE 過濾索引

我有一個表，在 SQL Server 2012 企業版中，我希望有一個唯一索引IGNORE_DUP_KEY = ON，但我只希望這個索引（並忽略重複項）僅應用於表的子集 - 僅行FLAG = 1。

CREATE TABLE [dbo].[FOO]
([ID] NOT NULL,
[Value] [int] NULL,
[FLAG] [bit] NULL)

嘗試使用以下查詢創建索引時

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UQ_FL_FOO_VALUE] ON [dbo].[FOO]
([Value] ASC)
WHERE [flag] = 1
WITH (IGNORE_DUP_KEY = ON)

我收到一個錯誤：

消息 10618，級別 16，狀態 1，第 1 行無法在表 ‘dbo.FOO’ 上創建過濾索引 ‘IDX_UQ_FL_FOO_VALUE’，因為該語句將 IGNORE_DUP_KEY 選項設置為 ON。重寫該語句，使其不使用 IGNORE_DUP_KEY 選項。

是否無法將過濾的唯一索引與IGNORE_DUP_KEY = ON?

將索引設置為非唯一不是一種選擇。我需要它是唯一的以過濾重複的插入，但僅在插入帶有FLAG = 1. 我需要忽略重複行的插入，而不是失敗。

根據MSDN，該IGNORE_DUP_KEY選項不適用於過濾索引：

IGNORE_DUP_KEY不能為…過濾索引設置為 ON。

正如其他答案已經提到的那樣，過濾索引不支持 IGNORE_DUP_KEY ，這在手冊中有明確說明：

過濾索引不允許 IGNORE_DUP_KEY 選項。

但是，如果您可以對錶的結構進行小幅更改，則有一種解決方法。在 uniquifier 列的幫助下，您可以創建一個唯一索引，該索引將有效地允許您Value僅對具有FLAG = 1.

下面假設 ID 列已經被聲明為唯一的（或者是主鍵或者有一個 UNIQUE 約束）。首先，添加一個計算列，該列將評估為 NULL 的FLAG = 1行和ID所有其他行：

ALTER TABLE dbo.FOO
ADD Uniquifier AS CASE FLAG WHEN 1 THEN NULL ELSE ID END;

(Value, Uniquifier)現在您可以使用以下選項創建唯一索引IGNORE_DUP_KEY：

CREATE UNIQUE NONCLUSTERED INDEX UQ_1
ON dbo.FOO (Value, Uniquifier)
WITH (IGNORE_DUP_KEY = ON);

由於Uniquifier帶有 的行將為 NULL FLAG = 1，因此唯一性將基於Value單獨確定。所有其他行都將是唯一的，Value因為Uniquifier它是唯一的（因為它的值取自唯一的列，ID）。

SQL Fiddle上提供了該方法的現場展示。

引用自：https://dba.stackexchange.com/questions/128559