Converting non-clustered index to clustered index

Question

Converting non-clustered index to clustered index

Eswin

SSCrazy Eights

Points: 8320
More actions
June 23, 2009 at 11:31 pm

#155870

Hi Expertz
I have a table that contains the employee details the company.
There is column emp_id which is the primary key.
But there is no "clustered index" for the table. So to improve the performance i need to create a clustered index.
Since there is already a non-clustered index for the primary key constraint on emp_id i need to convert this non-clustered index "PK__Employees__1D4655FB" to a clustered index.
CREATE UNIQUE CLUSTERED
INDEX [PK__Employees__1D4655FB] ON [dbo].[employee_tbl] ([emp_id])
WITH
DROP_EXISTING
ON [PRIMARY]
When i use the above TSQl im getting an error
Server: Msg 1925, Level 16, State 2, Line 1
Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.
When i tried to drop the non-clustered index "PK__Employees__1D4655FB"
Drop INDEX [dbo].[employee_tbl].[PK__Employees__1D4655FB]
im getting the error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.employee_tbl.PK__Employees__1D4655FB'. It is being used for PRIMARY KEY constraint enforcement.
And lot of tables and stored procedures depend on this primary key (emp_id).
Please help..........
Tanx 😀

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

When you specify the PK for a table, unless you explicitly ask for a non-clustered PK, it will automatically create a clustered index on the PK.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What does this return?

select name, type_desc from sys.indexes where name = 'PK__Employees__1D4655FB'

If it does return nonclustered, then to make it a clustered index you'll have to drop the primary key which means dropping all foreign keys that reference the table, recreate the primary key and all of the foreign keys.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eswin SSCrazy Eights Points: 8320 More actions · Answer 3

GilaMonster (6/24/2009)
What does this return?
select name, type_desc from sys.indexes where name = 'PK__Employees__1D4655FB'

It returns NONCLUSTERED .

Is there any other way other than dropping the primary key and the foreign keys.

How to get all the foreign keys that references this primary key.

Tanx 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Eswin (6/24/2009)
Is there any other way other than dropping the primary key and the foreign keys.

Not in 2000 I believe.

How to get all the foreign keys that references this primary key.

Query sysforeignkeys.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Eswin (6/23/2009)
Since there is already a non-clustered index for the primary key constraint on emp_id i need to convert this non-clustered index "PK__Employees__1D4655FB" to a clustered index.
CREATE UNIQUE CLUSTERED
INDEX [PK__Employees__1D4655FB] ON [dbo].[employee_tbl] ([emp_id])
WITH
DROP_EXISTING
ON [PRIMARY]
When i use the above TSQl im getting an error
Server: Msg 1925, Level 16, State 2, Line 1
Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.

Wait a minute... either you're not telling the whole story or something else is wrong. The quote above is from your first post and it indicates that the index is already clustered.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 6

Hi jeff,

The story is i have non-clustered PK .

I want to make this non-clustered PK to clustered PK in sql server 2000 because it will improve performance i guess.

When i edit the non-clustered PK and check it as clustered i get this

error.

TSQl i gave is what it generates for the execution.

any suggestions.........

Tanx 😀

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Eswin (6/24/2009)
Hi jeff,
The story is i have non-clustered PK .
I want to make this non-clustered PK to clustered PK in sql server 2000 because it will improve performance i guess.
When i edit the non-clustered PK and check it as clustered i get this
error.
TSQl i gave is what it generates for the execution.
any suggestions.........

I guess I'd just try dropping the index without the DROP_Existing. The PK constraint should preserve the DRI on the FK's.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Dropping an index that's used for enforcing of a pk constraint isn't allowed. To drop the index that's enforcing a pk, the pk itself must be dropped/

CREATE TABLE Test1 (

id int identity,

Somestr varchar(10)

)

ALTER TABLE Test1 ADD COnstraint pk_Test PRIMARY KEY (ID)

GO

Drop index Test1.pk_Test

Msg 3723, Level 16, State 4, Line 1

An explicit DROP INDEX is not allowed on index 'Test1.pk_Test'. It is being used for PRIMARY KEY constraint enforcement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Moutray SSCertifiable Points: 5956 More actions · Answer 9

I have this same problem in SQL 2005.

Our vendor (who shall remain nameless) made EVERY primary key in the system a NON-CLUSTERED INDEX.

Usually they have created a CLUSTERED INDEX on fields other than the PRIMARY KEY field.

Someone has told them this is just how databases are built. I wish I knew why.

However, I have one particularly glaring problem with a table where the PRIMARY KEY is NONCLUSTERED, but THERE IS NO CLUSTERED INDEX ON THE TABLE AT ALL!

To make matters worse, there are several tables with FOREIGN KEY constraints referencing the primary key field on this table. (This particular vendor hardly ever uses foreign key constraints, but in this particular instance, they put a foreign key constraint on every table referencing this. That happens to be a lot of tables.)

The following command, at least in SQL 2005 will change a nonclustered primary key index to a clustered primary key index without requiring you to drop the primary key, but it won't work if you have foreign keys dependent on this primary key.

CREATE UNIQUE CLUSTERED INDEX [PK_Whatever] ON [Whatever]([WhateverID])

WITH DROP_EXISTING

This doesn't help me, but perhaps it will help you.