CREATE INDEX NotAllowed
ON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
Msg 1916, Level 16, State 4, Line 1
CREATE INDEX options nonunique and ignore_dup_key are mutually exclusive.
ignore_dup_key should come with Unique key.
CREATE UNIQUE INDEX NotAllowed
Maybe I am misunderstanding you - isn't this exactly as I say in the answer explanation?
Good question though. I used this option mostly while merging data from different server.tables into my reporting server, where it won't insert duplicate data.
Thanks for the kind words.
Situations such as what you describe, also known as staging tables, are indeed about the only place where I would consider using this option (though even in that case, I would probably prefer a DISTINCT and/or NOT EXISTS to prevent duplicates - unless that would cause too many performance problems).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/