Why is Ignore Duplicate Keys not allowed on a Unique Key?

Question

If I understand this answer correctly the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic.

In the MSSQL interface when I switch to a Unique Key the Ignore Duplicate Keys gets disabled.

Am I understanding something wrong? I would like to use Ignore Duplicate Keys in this case. What are the consequences when I do an Index with Is Unique versus a Unique Key.

Type Index with Is Unique (dropdown enabled):

Index with IsUnique On

Type Unique Key (dropdown disabled):

Unique Key

• nbk: UNIQUE means unique every value can only exist once in the column, so enabling Ignore Duplicate Keys makes absolutely no sense whatsoever
• Erik Reasonable Rates Darling: @nbk it allows you to maintain a unique index and disallow conflicting entries without an error being raised. It does not allow duplicate keys into a unique index.
• nbk: the pint stands, even for an index ait doesn't make any sense to allow duplicates, if no dupliates can be entered why still allowing itß
• Dirk Boer: @nbk this is an application where I have large amount of concurrent users (+30.000) - I just need to ensure a large batch of rows exist at some point in time. BulkInsert seems to have a much higher performance so I use that. The idea is that this saves performance, because the duplicate check is anyway done. Otherwise it's done twice. Once by me, once by SQL.
• Charlieface: @ITThugNinja You can point FKs at both in SQL Server. And the doc you linked refers to indexes not constraints. As you can see, this option is disallowed on constraints. dbfiddle.uk/p7zjHobN

Answer 1

the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic.

No, the point is that unique constraints are just declarative entities, you simply declare that some fields or their combination in your table is unique and you don't care how this will be implemented, so for example my colleagues-developers that uses some Entity Framework just make some clicks on a table without thinking what they really create and this will work in any RDBMS. So they don't even know about indexes but have basic database knowlege and this permites them to declare uniqueness.

Here comes on the physical implementation, every unique constraint is implemented by creating the simplest unique index on these fields and for application developer it can be enough but you may want to have full control of this as you want to use it as index and not only constraint, index is another entity with more options you can use, for example you may want to include some field in the include clause of this unique index as all your queries retrieve this additional field by passing the unique key value, you just do not want to maintain too similar indexes and with the constraint declaration you cannot do this. Another example is that option you wanted to use, ignore_dup_key, or you just want to make unique all the values that are not null, in this case you also use unique index and cannot use just a unique declaration, and your filtered index definition maybe more complex, you may want to enforce uniqueness for only one client id and you create your unique filtered index only for this specific ClientId