Identity

Question

Post reply

Identity

Tara-1044200

SSCoach

Points: 15951
More actions
June 15, 2010 at 7:20 am

#97050

I have a production table with Identity column and few databases are built with reference to this ID column but now i have to delete duplicates from this table but I might lose reference in other databses. What would be the best approach to handle this by deleting duplicates.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

1. Identify duplicates.

2. For each duplicate, identify which one should be kept.

3. For each other table, update the duplicates with the value being kept.

4. Delete the duplicates.

I'd suggest a backup of the original data ... just in case.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

If possible, I'd also wrap any deletes in a transaction, along with a test to see if things work before you commit the transaction.

Pete Cox SSChasing Mays Points: 636 More actions · Answer 3

I guess that you're talking about a SQLServer-supplied auto-increment IDENTITY column?

If so, would now be a good time to discover the real key of the referenced table?

Otherwise, make sure you keep a copy of the clean-up process, so that you can use it next time you get duplicates, and the time after that, and... 🙂

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

i have some confusion here , if the column is identity--auto incremented then how duplicates records generated ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

The identity is not guarenteed to be unique. You can insert values into the identity column with SET IDENTITY_INSERT ON. They could possibly be duplicates.