drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
Thanks
Have you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks
|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks