change index (PK) from NONCLUSTERED to CLUSTERED

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

change index (PK) from NONCLUSTERED to CLUSTERED

Author

Topic

marginerazvan
Starting Member

14 Posts

Posted - 2010-10-01 : 03:12:01

Hello there,
I created a table with PK set on UNCLUSTERED (my mistake)
I need to set it to CLUSTERED.

Here is table definition:
CREATE TABLE FITTING (
TRANS_ID int NOT NULL PRIMARY KEY NONCLUSTERED,
....other columns definitions
)
GO

I know how to find the INDEX name of the colum (search through sys.indexes and sys.tables). Let's presume it is PK__FITTING__136804E7468862B0

How should I change it in CLUSTERED?
DROP INDEX not allows me to delete an index made for PK.

Thank you in advance,
Razvan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-01 : 04:13:22

one way is to drop the primary key constraint. I will internally drop the index.
Then again you need to create the primary key.

Try
ALTER TABLE <YourTable> DROP CONSTRAINT <PK_constraint>

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-01 : 04:26:30

Drop all referencing foreign keys
Drop the primary key
Recreate the primary key as clustered
Recreate all foreign keys

Preferably do it when no one is using the system so there's less chance of data being inserted that violates the constraints during the process.

--
Gail Shaw
SQL Server MVP

marginerazvan
Starting Member

14 Posts

Posted - 2010-10-01 : 05:36:26

Mission accomplished.

- delete all foreign keys who pointed to FITTING.TRANS_ID

- delete NONCLUSTERED index
DECLARE @IndexName varchar(100)
SELECT @IndexName = sys.indexes.name FROM sys.tables INNER JOIN sys.indexes ON sys.tables.object_id = sys.indexes.object_id WHERE sys.tables.name = 'FITTING' AND sys.indexes.type = 2
EXEC sp_rename @IndexName, 'df_to_drop', 'OBJECT'
ALTER TABLE FITTING DROP CONSTRAINT df_to_drop
GO

- add CLUSTERED index
ALTER TABLE FITTING ADD CONSTRAINT PK_FITTING_TRANS_ID PRIMARY KEY (TRANS_ID)
GO

- create again all foreign keys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources