0

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

9
  • UNIQUE means unique every value can only exist once in the column, so enabling Ignore Duplicate Keys makes absolutely no sense whatsoever
    – nbk
    Commented Feb 28 at 23:20
  • @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. Commented Feb 28 at 23:28
  • 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ß
    – nbk
    Commented Feb 29 at 0:15
  • @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.
    – Dirk Boer
    Commented Feb 29 at 1:05
  • 1
    @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 Commented Feb 29 at 2:02

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.