• This option breaks the ACID properties of SQL Server.
• This option can cause nondeterministic results.
• This option may only be specified for unique indexes

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. This breaks atomicity (one of the ACID properties), since a transaction is only atomic if it either completes completely, or fails completely.

If a key value already exists in the destination and that same key value is in the source, then none of the source rows are accepted. This is deterministic. However, if a key value is not in the destination but twice or more in the source, then the first row processed is accepted and the others are not - and which row is processed first is nondeterministic.

The IGNORE_DUP_KEY option makes no sense for a nonunique index, and may not be specified in a CREATE INDEX statement without the UNIQUE keyword; SQL Server will return error 1916 if you attempt to violate this rule. To my surprise, the syntax for CREATE TABLE and ALTER TABLE does allow you to specify the IGNORE_DUP_KEY option for PRIMARY KEY and UNIQUE constraints.

The ANSI standard contains nothing about indexes; they are considered an implementation detail and database vendors have complete freedom in this area. As such, this option is not technically in violation of the ANSI standard (though the possibility of specifying it for a PRIMARY KEY or UNIQUE constraint does constitute a violation of the standard, and the behaviour of this option does go against the ACID properties, one of the basic principles of relational databases).