The ignore_dup_key and ignore_dup_row options to index creation are
useful. But it seems like the most common thing you'd want to do is
disallowed:

create unique clustered index idx
on my_table (foo, bar)
with ignore_dup_row

Msg 1916, Level 16, State 2
Server 'MY_SERVER', Line 1
CREATE INDEX options unique and ignore_dup_row are mutually exclusive.

I am aware that one can select into a temporary table, or 'select
distinct' or whatever, but this is a moderately large table and I'd
prefer not to use tempdb.

I could create a nonunique clustered index, then drop that and create
a clustered index. For that second index the sorted_data option would
work. But still it seems wasteful. Is there no way to create a
unique index discarding duplicate rows? (Two rows which were not
duplicates but had the same key columns would be an error of course.)

Or for that matter I'd be happy with making a unique index ignoring
duplicate rows - so that no two rows can have the same key columns
unless they are exact duplicates.

--
Ed Avis <ed (AT) membled (DOT) com>

It looks like you should put in an ISUG enhancement request, if you are a
member, or post at the product_futures_discussion. I used to have this
option with Britton-Lee (later ShareBase), which is where Sybase spun off
from. I would think that the underlying Sybase architecture hasn't changed
that much since then. Back then, the delete_dups option could be specified
for either clustered or nonclustered indexes.

At index creation time a warning would be generated for a clustered index if
duplicates existed. The "first" row for a unique key is kept and the others
are discarded. (Entry point for Joe Celko to rant.) Note that duplicate
rows would not exist and it appears as if you would want them kept. I found
this useful when batch data was inadvertently loaded more than once -
similar to your scenario I suspect. (Load all of the batches into an
un-indexed table and then create the clustered index with this option.)

There were neither warnings nor errors for nonclustered indexes. (In other
words, the option is ignored at index creation.)

At insert (or update key) time non-unique modifications would generate
warnings for the duplicates, which were not updated/inserted, and
non-duplicates would be updated/inserted. Unfortunately, in my opinion,
this became a cheap way for programmers to avoid writing SQL that did not
insert non-duplicate rows - just insert a superset of data and let the index
discard the duplicate data.


"Ed Avis" <ed (AT) membled (DOT) com> wrote