什麼時候應該在索引上使用 IGNORE_DUP_KEY 選項？

有人說最好製作查詢以避免重複的鍵異常，但我不相信這比僅設置IGNORE_DUP_KEY = ON索引更有效。

我的目標是在嘗試更新這些行之前確保一個或多個使用者存在一行或一組行。我這樣做，以便當我嘗試使用如下所示的更新語句更新行並且沒有行受到影響時，這是因為[Count]謂詞的部分不滿足，而不是根本不存在的行（即[ID]不滿足謂詞的部分）：

UPDATE [Inventory]
SET [Count] = [Count] + 1
WHERE [ID] = 3 
AND ([Count] + 1) <= @MaxInventory

我可以執行EXISTS(SELECT 1 From [Inventory] WHERE [ID] = 3以檢查該單行，並且僅在該行不存在時才插入該行。這只是避免了不必要的插入。如有必要，插入仍然必須與並發事務競爭，因此仍然可能發生重複鍵異常。

我很好奇IGNORE_DUP_KEY在這種情況下僅打開而不是允許拋出和擷取錯誤是否更高效。具體來說，我很好奇它是否與執行存在檢查一樣快甚至可能更快，只是嘗試插入記錄並讓它忽略重複的鍵。

當我一次檢查和初始化多個記錄時，這變得更加重要。例如，如果我需要確保在單個更新語句中存在數千個使用者的記錄，那麼如果我只是預先執行該插入語句，讓它忽略重複的鍵，那麼邏輯會簡單得多。避免重複會更複雜，因為我必須首先查詢不存在記錄的表，然後嘗試僅添加這些記錄（同樣，忽略重複鍵）。即使所有記錄都存在，僅插入可能會更快。

我可以在中途遇到它並檢查是否缺少任何記錄，例如左連接或COUNT比較，但是如果忽略重複鍵的插入更快，為什麼還要麻煩呢？

IGNORE_DUP_KEY使用並嘗試插入而不是提前檢查行是否存在是一個好主意嗎？如果不是，為什麼？

這絕對是為索引啟用的非典型設置。我認為您不會找到很多人跳起來談論使用它。

當然，Paul White 有幾篇關於這個主題的有用文章：

• IGNORE_DUP_KEY 在聚集索引上較慢
• IGNORE_DUP_KEY 的創造性使用

如果您關心的是 upsert 模式（或類似的東西），Michael Swart 的這篇文章非常有用：

• SQL Server UPSERT 模式和反模式

每個功能都有一個案例，IGNORE_DUP_KEY也不例外。

正如我在IGNORE_DUP_KEY 在聚集索引上較慢中解釋的那樣，此選項僅在重複鍵異常的數量足夠少時才可能提高性能（在聚集索引上）。

收支平衡點取決於系統，需要實際測試才能正確評估。盡量不要提前做出假設或判斷。仔細測試每個健壯的實現替代方案，並選擇在您的場景中最有意義的一個。

作為導致性能意外的原因的範例，請考慮索引插入點必須位於某個階段，因此重複該操作可能比預期的要便宜。當 SQL Server 可以使用“行集共享”優化時，成本就更少了。引擎在檢查是否存在時定位插入點一次，並為插入操作保留該直接引用。

雖然與問題沒有直接關係，但我應該提到一些使用注意事項IGNORE_DUP_KEY：

• MERGE語句的插入活動不尊重它。
• 它不能添加到現有索引。
• 它不像顯式存在測試那樣“可發現”。

我通常更喜歡單獨的語句MERGE而不是 ，但您應該針對您的預期用途對其進行測試。MERGE可以將插入和更新組合到單個語句中，同時利用漏洞填充優化和行集共享。

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