Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Duplicate Record
Add to briefcase
14 posts, Page 1 of 2
12»»

Duplicate Record Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sudhakara
Posted Friday, June 04, 2010 9:03 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 588, Visits: 1,155
Hi,

While running the rebuild index on the database it has got failed with below error message.

Rebuilding indexes for table 'Mytable' [Microsoft SQL-DMO (ODBC SQLState: 23000)] Error 1505: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Here my question how is it possible to insert or update duplicate key on the key column of unique clustered index.
SQL server 2000 with SP4.

Please help me to simulate and resolve this issue.

Thanks in Advance.
Post #933119
magasvs
Posted Saturday, June 05, 2010 9:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:54 AM
Points: 1,259, Visits: 700
Try to recreate index with option IGNORE_DUP_KEY = ON (CREATE INDEX ... WITH DROP_EXISTING = ON, IGNORE_DUP_KEY = ON )
Post #933155
PaulB-TheOneAndOnly
Posted Saturday, June 05, 2010 1:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 2,988, Visits: 4,412
or... fix the data.

1st figure it out which PKs are duplicate...
select  * from(
select  my_primary_key_column, count(*) as headcount
from    my_table
group by my_primary_key_column
)
where   headcount > 1

2nd take a look at duplicate rows...
select  *
from    my_table
where   my_primary_key_column = whatever_you_got_in_previous_query

3rd talk to the business and fix it - most probably by deleting unwanted rows

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #933172
sudhakara
Posted Saturday, June 05, 2010 7:47 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 588, Visits: 1,155
magasvs (6/5/2010)
Try to recreate index with option IGNORE_DUP_KEY = ON (CREATE INDEX ... WITH DROP_EXISTING = ON, IGNORE_DUP_KEY = ON )


Thanks for yours reply.

How is it possible to update or insert the duplicated on the table columns which has the unique clustered index ?
Post #933192
LutzM
Posted Sunday, June 06, 2010 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131
Would you please clarify what you're trying to do?
Will you either end up without duplicates or do you want to insert duplicate values?
If the former, follow Pablos advice. If the latter, a unique index will be the wrong choice.


Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #933212
homebrew01
Posted Sunday, June 06, 2010 8:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:41 PM
Points: 2,554, Visits: 7,213
I interpreted the situation to be that there are already duplicate records, and the OP was wondering how that's possible since the index being rebuilt disallowed duplicates. So, duplicates should not have been created in the first place.


Post #933237
Jeff Moden
Posted Sunday, June 06, 2010 4:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811
It is possible that someone disabled the unique constraint, added data, and then reenabled the constraint with WITH NOCHECK activated (IIRC).

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #933292
sudhakara
Posted Monday, June 07, 2010 3:00 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 588, Visits: 1,155
lmu92 (6/6/2010)
Would you please clarify what you're trying to do?
Will you either end up without duplicates or do you want to insert duplicate values?
If the former, follow Pablos advice. If the latter, a unique index will be the wrong choice.


Maintanance paln trying to rebuild the index.but it is not successed since the duplicate keys are there.i am not trying to do any dml operations on the table.
Post #933376
sudhakara
Posted Monday, June 07, 2010 3:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 588, Visits: 1,155
Jeff Moden (6/6/2010)
It is possible that someone disabled the unique constraint, added data, and then reenabled the constraint with WITH NOCHECK activated (IIRC).


As per your post i have tried the but its failing.Please check the following code which i used to verfity

--created table
create table duplicaterecord(a int not null primary key)

--Insert Record
insert duplicaterecord
select 1

--Disable the constratins
ALTER TABLE duplicaterecord NOCHECK CONSTRAINT PK__duplicaterecord__0AD2A005

--Inserting duplicate record
insert duplicaterecord
select 1

--Getting error message
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__duplicaterecord__0AD2A005'. Cannot insert duplicate key in object 'dbo.duplicaterecord'.
The statement has been terminated.

Please check and post your comments.

Thanks in Advances.
Post #933378
sudhakara
Posted Tuesday, June 08, 2010 2:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 588, Visits: 1,155
Hi,

Any update on this ?


Thanks in Advance.
Post #933838
« Prev Topic | Next Topic »

Add to briefcase
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.