Rebuild index results in duplicate key violation

This is SQL 2005. I ran the Rebuild Index routine and it errors on one of the databases. The error is a violation of the primary key on a table. Somehow there is a duplicate record in the table. How do I find which records are duplicates and rebuild the index?
rwheeler23Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
You can simply group on key columns and check if there are any duplicates.
SELECT pk_col1, pk_col2 -- list all your pk_columns
       ,COUNT(*) cnt
  FROM your_table
 GROUP  BY pk_col1, pk_col2 -- list all your pk_columns
HAVING COUNT(*) > 1

Open in new window

0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
You can use below example to find out duplicate values..

SELECT
    id, orgName
FROM (
     SELECT
         orgName, id,
         ROW_NUMBER() OVER (PARTITION BY orgName ORDER BY id) rnk
     FROM organizations
) AS d
WHERE rnk> 1

Let me know if you need more info here !
0
 
rwheeler23Author Commented:
There are twelve databases with this table. I have run both of these on all databases and no results are returned. I am going to run rebuild index on each database individually.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Pawan KumarDatabase ExpertCommented:
Great ! let us know in case of any issue.
0
 
rwheeler23Author Commented:
Fortunately the rebuild index task tells you then record causing the issue. It happened in only one database and there were actually 6 duplicate records. Once I removed the duplicates the rebuild task finished without error. Very odd that duplicate records were allowed in the first place since they violated the primary key.
0
 
Pawan KumarDatabase ExpertCommented:
Great ! Please go ahead and close the question and mark one or more comments as accepted solution. !!

Keep Learning.
0
 
rwheeler23Author Commented:
Thanks for your help.
0
 
rwheeler23Author Commented:
Both solutions were equally helpful.
0
 
rwheeler23Author Commented:
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.