Regarding Existing Duplicate Key

Question

Regarding Existing Duplicate Key

darklight_arizona

SSC Journeyman

Points: 96
More actions
May 10, 2016 at 3:47 am

#310476

Thanking You all
Is there any way to create a unique key on a column in SQL SERVER 2008 without removing duplicates rows. We can't remove duplicate rows due to some business logic applied on it.
Can we create unique columns and make index non unique. Is there any solution for this?.
Your suggestion will be highly appreciated.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 1

Why can't you remove duplicates? What is their purpose?

You can add an incremental ID, make that primary key. 2 duplicate rows cannot have the same PK however, it must be unique.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

darklight_arizona (5/10/2016)
Is there any way to create a unique key on a column in SQL SERVER 2008 without removing duplicates rows. We can't remove duplicate rows due to some business logic applied on it.

If you want to create a unique nonclustered index on a column, the values in it must be unique. If they aren't, the only way I'm aware of is to create it is to make it untrusted, which I don't like at all. After all, if you're going to create a unique index, why would you want to if you aren't going to enforce it?

Can we create unique columns and make index non unique. Is there any solution for this?

I'm not sure how to read this.

Can you create a new column that is unique? Of course.

Can you create a nonclustered index that's not unique? Of course.

The bottom line is to ask yourself why you want to create the index in the first place. If you need to have duplicate values, then a unique index is not appropriate. If you want to create it to cover queries or a foreign key, then a normal NCI would be the appropriate choice. Can you tell us why you want to create it?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Smendle Hall of Fame Points: 3969 More actions · Answer 3

darklight_arizona (5/10/2016)
Thanking You all
Is there any way to create a unique key on a column in SQL SERVER 2008 without removing duplicates rows. We can't remove duplicate rows due to some business logic applied on it.
Can we create unique columns and make index non unique. Is there any solution for this?.
Your suggestion will be highly appreciated.
Thanks

If there is no combination of columns that will, when combined, create a unique record then adding a unique column value like an auto-generating number is the only way to keep those duplicates(pre unique column) but still create a unique key value.

You could also create an autonumber without creating a new column in the table but it would involve changing the data calls to that table to utilize the ROW_NUMBER() function... please note this would not be usable for anything except during the session in which the data call was made. To follow standard ACID (mainly Durable) guidelines you should create a column (or columns) with a unique value.

i.e.

SELECT Col1, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY Col1) as rowid FROM table1

Eric M Russell SSC Guru Points: 125596 More actions · Answer 4

Perhaps what you need is a composite unique key, which would be the business code combined with something like a date/time column. For example, let's assume an Employee table is inserted with another row whenever an employee is re-hired after having previously left the company.

CREATE UNIQUE INDEX UIX_Employee on Employee ( EmployeeID, StartDate );

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 5

One possibility would be to add a new int field to the table with a default value of 1. After adding that populate it with a ROW_NUMBER() over the fields you want to be unique. Then go ahead and create the unique index you want but include the new ID column in it.

It's non ideal but it will let you keep the duplicates you currently have and as long no one enters data directly into the id column it'll prevent new duplicates.