Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Duplicate rows indentified while creating an unique non-clustered index

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 Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question