:::: MENU ::::

DML statements affected by the IGNORE_DUP_KEY = ON index option

  • Nov 17 / 2008
  • 0
Constraints and Keys, dbDigger

DML statements affected by the IGNORE_DUP_KEY = ON index option

Question: Which DML statements are affected by the IGNORE_DUP_KEY = ON index option in SQL Server 2008?

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.

Consult us to explore the Databases. Contact us