clustered index - SQL Server Forums

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

SQL Server Administration (2008)

clustered index

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-14 : 12:45:05

I have a db with some tables with non-clustered indexes on the PK and some with clustered indexes on the PK.

I want to change the non-clustered ones to be clustered on the PK. How can I do this easily?

I've tried adding a clustered index, removing the primary key then adding it again but the PK then becomes non-clustered.

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 12:50:03

drop any index that refers to that column, clustered or not. Then build the clustered index on your PK column. It sounds very much like your table may be overly indexed. Multiple indexes referring to the same column is generally a bad idea. I would check in the DMV's and see which ones are even used.

Mike
"oh, that monkey is going to pay"

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-14 : 12:51:28

Whats the sql to build a clustered index on the pk column please?

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-14 : 12:55:38

Create clustered index ixc_myindex on tablename(fieldname)

Mike
"oh, that monkey is going to pay"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 13:03:00

Primary key creates clustered index by default unless you specify NON-CLUSTERED or if there is already Clustered index existing.

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-14 : 15:42:22

Thanks for that

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-14 : 15:44:51

Do any of you know a script that I could run that would remove the primary key then add it again on the same column? This would save me from having to manually remove it and re-create it each time?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 16:56:17

quote:
Originally posted by bpsintl

Do any of you know a script that I could run that would remove the primary key then add it again on the same column? This would save me from having to manually remove it and re-create it each time?

Remember you can only have 1 clustered index in table and if you want PK to have clustered then it shouldn't conflict with other column.

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-14 : 17:00:27

I know, I have no clustered indexes so I want a script that will remove the PK, then create it on the same column, hence creating the clustered index automatically

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-15 : 06:33:21

Anyone?

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 07:01:14

USE [master]
GO
--Create a dummy table.
Create table testing(mycol varchar(20) primary key)
GO
--View it's properties
sp_help testing
GO
--Remove the Primary Key Constraint
ALTER TABLE [dbo].[testing] DROP CONSTRAINT [PK__testing__4A28471B5772F790]
GO
--Add a primary key constraint
Alter table testing add CONSTRAINT colpk PRIMARY KEY (mycol)

Mike
"oh, that monkey is going to pay"

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-15 : 07:56:39

Thanks but I meant a script that would do all tables automatically. ie I run the script, it detects the pk column, removes it and re-creates it on all tables, one after the other?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 08:40:29

quote:
Originally posted by bpsintl

Thanks but I meant a script that would do all tables automatically. ie I run the script, it detects the pk column, removes it and re-creates it on all tables, one after the other?

There is no such thing automatic.You have to write your own script for your need.

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-15 : 08:44:28

No problem, thanks anyway

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-15 : 10:51:34

Well you could use some dynamic sql against the information schema tables to generate the script for you.

select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Mike
"oh, that monkey is going to pay"

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-15 : 11:38:02

It's ok, I went through them all manually

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources