CREATE INDEX NotAllowed

ON Dest (Value)

WITH(IGNORE_DUP_KEY = ON);

go

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

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.