Duplicate rows indentified while creating an unique non-clustered index

[DilipVoora]

Dear Experts,

When are trying to recreate an unique non-clustered index over a table, it got failed after 4+ hours with an error message "unique non-clustered index got failed due to duplicate rows identified". Can anyone please let me know the way to extract those duplicate rows from the table and also a solution to fix this issue?

Regards,

Dilip Voora

[former_member188958 · Accepted solution]

To find the sets of keys with duplicates,

select <keys>, count(*) 

from <tablename>
group by <keys>
having count(*) > 1

You will then want to look at the full rows that have duplicate keys to decide how to handle them (perhaps by deleting some of the rows).

[DilipVoora · Accepted solution]

Hi Bret,

Can you please explain me with an example?

Regards,

Dilip Voora

[Former Member · Accepted solution]

Just take Brets sql

select <keys>, count(*)

from <tablename>
group by <keys>
having count(*) > 1

and change the word <keys> into your list of column in the unique index

and change the word <tablename> for the table you're creating the index on.

Run the SQL and you'll see the list of rows which fail due to a unique index violation.

[former_member188958 · Accepted solution]

Example:  suppose you are a car company, and have a table of the available colors for each model:

create table cars(id int identity, model varchar(20), color varchar(20))

go

insert cars values ("A", "Blue")

insert cars values ("B", "Blue")

insert cars values ("B", "Green")

insert cars values ("C", "Blue")

insert cars values ("C", "Green")

insert cars values ("C", "Blue")

go

The marketing department makes a business rule that distinctive color names must be used for every model, so you try creating a unique index to enforce this rule

create unique index i1 on cars(color)

go

Msg 1505, Level 16, State 2:

Server 'rel16sp02_bret_sun2', Line 2:

Create unique index aborted on duplicate key.  Primary key is '"Blue"'

To find the colors that are used more than once

select id, model, color from cars

group by color having count(*) > 1

order by color, model

go

id          model                color

----------- -------------------- --------------------

           1 A                    Blue

           2 B                    Blue

           4 C                    Blue

           6 C                    Blue

           3 B                    Green

           5 C                    Green

(6 rows affected)

To solve the problem of having duplicates, we have to look at the set of rows

for each duplicate color and decide what to do.

Rows 4 and 6 have the same model and color, we can delete one of them (although if the id is referenced in other tables in the database, those other tables might also need to be updated)

delete cars where id = 6

go

The remaining duplicates can be handled by updating the color name to something more distinctive:

update cars set color = "Aqua Blue" where id = 1

update cars set color = "Blueberry" where id = 2

update cars set color = "Cornflower" where id = 4

update cars set color = "Emerald" where id = 3

go

Now the create index works without error:

create unique index i1 on cars(color)

go

-bret

[DilipVoora · Accepted solution]

Thanks a lot Bret.