Using Unique Indexes

[Bob]

Why, in the process of creating a unique index, does SQL Server allow
me to select the "Ignore duplicate keys" option? Wouldn't I just
create a non-unique index if I wanted to ignore duplicate keys? I
came across this fact while preparing for the SQL Server design exam.

[David Portas]

The IGNORE_DUP_KEY option on a unique index defines what happens when you
attempt to insert a row with a duplicate key.

With or without this option duplicate rows are of course not permitted in
unique indexes. Without the IGNORE_DUP_KEY option the entire INSERT will
fail. With IGNORE_DUP_KEY only the duplicated row(s) will fail.

With IGNORE_DUP_KEY, if you insert more than one duplicate row in a single
insert statement then one of them will be inserted (provided a duplicate row
doesn't already exist in the table) and the rest will be ignored. In that
situation you cannot control which row will be inserted and which
duplicate(s) will be ignored.

Because the behaviour of this option is so peculiar and non-standard I
recommend you avoid it. I can't think of a good reason for using this option
at all. If you must use it then do so with care. When writing code that
INSERTs to a table indexed in that way you need to consider the possible
implications for data integrity if only part of your data is added to the
table. In my view it's a lot less trouble to write code that doesn't insert
duplicates in the first place and to do without IGNORE_DUP_KEY.

Finally, a unique *constraint* is normally preferred over a unique *index*
and unique constraints don't allow this option.

--
David Portas
SQL Server MVP
--

[Steve Kass]

David,

I've used this, albeit rarely, for tables that get their data from
sloppy sources that include duplicates and near duplicates. If lots
of junk comes in, and I just need one row of the junk for each (you
name the key), this does the trick when yelling at the provider doesn't.

Steve Kass
Drew University

David Portas wrote:

The IGNORE_DUP_KEY option on a unique index defines what happens when you
attempt to insert a row with a duplicate key.

With or without this option duplicate rows are of course not permitted in
unique indexes. Without the IGNORE_DUP_KEY option the entire INSERT will
fail. With IGNORE_DUP_KEY only the duplicated row(s) will fail.

With IGNORE_DUP_KEY, if you insert more than one duplicate row in a single
insert statement then one of them will be inserted (provided a duplicate row
doesn't already exist in the table) and the rest will be ignored. In that
situation you cannot control which row will be inserted and which
duplicate(s) will be ignored.

Because the behaviour of this option is so peculiar and non-standard I
recommend you avoid it. I can't think of a good reason for using this option
at all. If you must use it then do so with care. When writing code that
INSERTs to a table indexed in that way you need to consider the possible
implications for data integrity if only part of your data is added to the
table. In my view it's a lot less trouble to write code that doesn't insert
duplicates in the first place and to do without IGNORE_DUP_KEY.

Finally, a unique *constraint* is normally preferred over a unique *index*
and unique constraints don't allow this option.

[David Portas]

Yes, I suppose that's one valid application of IGNORE_DUP_KEY, although you
then have to deal with the non-atomic nature of inserts if you have other
processes updating the same table. In my environment I usually prefer to do
that sort of data-cleansing using third-party ETL tools.

--
David Portas
SQL Server MVP
--