Answer: INSERT only
Explanation: The IGNORE_DUP_KEY option controls what happens if execution of a multi-row INSERT causes violation of uniqueness in an index. Default behaviour is to give an error message and undo the entire INSERT statement, but with IGNORE_DUP_KEY on, a warning is given and only the offending rows are refused and other rows are still inserted. The DELETE statement is of course not affected, since deleting rows can never cause violations of a unique index. The UPDATE statement could have been affected, but Microsoft has chosen not to do so. The MERGE statement is also unaffected by this option, though this is not documented very well, and I suspect this to be a bug.
The code below demonstrates most of the statements made here:
CREATE TABLE Src (ID int NOT NULL PRIMARY KEY, Value varchar(30) NOT NULL ) CREATE TABLE Dest (ID int NOT NULL, Value varchar(30) NOT NULL ) CREATE UNIQUE INDEX ix_Dest ON Dest(ID) WITH (IGNORE_DUP_KEY = ON); go -- Load some sample data INSERT INTO Dest (ID, Value) VALUES (1, 'Existing'),(3,'Also existing'); INSERT INTO Src (ID, Value) VALUES (1, 'Added'),(2, 'New value'),(2, 'Another new value'); -- Test to see that MERGE does not respect IGNORE_DUP_KEY MERGE INTO Dest USING Src ON Src.ID = Dest.ID WHEN NOT MATCHED THEN INSERT (ID, Value) VALUES (Src.ID, Src.Value); -- Show results SELECT * FROM Dest; -- Test to see that UPDATE is not affected by IGNORE_DUP_KEY UPDATE Dest SET ID = 3, Value = 'Updated'; SELECT * FROM Dest; -- Clean up DROP TABLE Dest, Src; GO
Note: The Question is taken from SQLServercentral.com.