Alter PK to Cluster

Question

Post reply

Alter PK to Cluster

Mel3405

SSCommitted

Points: 1904
More actions
August 9, 2010 at 6:40 am

#225899

This PK has lots of foreign key constraints so I would like to not have to drop and recreate anything.
Is there a way to alter?

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

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

blackbird Ten Centuries Points: 1127 More actions · Answer 1

Here is what MSDN said you can do:

http://msdn.microsoft.com/en-us/library/aa259130%28v=SQL.80%29.aspx

I'm not sure what you are trying to change about your primary key though (length of a column? which columns make up your pk?).

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

blackbird (8/9/2010)
Here is what MSDN said you can do:
http://msdn.microsoft.com/en-us/library/aa259130%28v=SQL.80%29.aspx

That link is for SQL 2000. Is it still relevant for SQL 2008?

I'm not sure what you are trying to change about your primary key though (length of a column? which columns make up your pk?).

From the title I would assume nonclustered index - clustered index

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

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

Melissa, I suspect that you're going to have to script all the foreign keys, drop the constraint and then recreate the whole lot. I tried altering a pk using CREATE INDEX ... WITH DROP_EXISTING, but it threw an error:

Msg 1907, Level 16, State 1, Line 1

Cannot recreate index '<index name>'. The new index definition does not match the constraint being enforced by the existing index.

This was even if the index was exactly the same column and type as the old.

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.mason SSCrazy Eights Points: 9585 More actions · Answer 4

Yep, this is why I NEVER use PRIMARY KEY CLUSTERED even IF I intend to cluster on that column. I am willing to burn the index space and index the column twice -- once for the PK and once for the cluster. That way if I need to move the cluster to something else, I don't hit this problem.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 5

Just re-read the original question, I agree with Gail it's non-clustered to clustered. OK, if you have PRIMARY KEY NONCLUSTERED you are in my opinion where you want to be. Make a separate clustered index on the same column or columns. You can index the column twice, and you don't really want clustering tied to the referential integrity of the table IMO.

Mel3405 SSCommitted Points: 1904 More actions · Answer 6

Thank you. You answered my questions.

I didn't think so but you never know if someone has found a way around this.

Thanks again.

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

jeff.mason (8/9/2010)
Yep, this is why I NEVER use PRIMARY KEY CLUSTERED even IF I intend to cluster on that column. I am willing to burn the index space and index the column twice -- once for the PK and once for the cluster. That way if I need to move the cluster to something else, I don't hit this problem.

Heh... Ironically, that's exactly why I almost always us PRIMARY KEY CLUSTERED... Once I build it, I want people to be able to change it only after great difficulty. 🙂

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Jeff Moden (8/15/2010)
jeff.mason (8/9/2010)
Yep, this is why I NEVER use PRIMARY KEY CLUSTERED even IF I intend to cluster on that column. I am willing to burn the index space and index the column twice -- once for the PK and once for the cluster. That way if I need to move the cluster to something else, I don't hit this problem.
Heh... Ironically, that's exactly why I almost always us PRIMARY KEY CLUSTERED... Once I build it, I want people to be able to change it only after great difficulty. 🙂

Building scripts for foreign keys and constraints is not an issue for experienced DBA or dev. I agree with Jeff here, I wouldn't want to allow moving cluster too easy task for everyone. I prefer people consult with DBA (or responsible senior dev) before making such changes for LIVE db.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 9

Uh, why would you let anyone but an experienced DBA be in charge of changing an index from clustered to non-clustered? At that point your problem would be that your access is too open. If the system is locked down enough, that is not an issue.

I work in an environment where EVERYTHING on live is subject to change control, even adding an index. The fewer changes required to do something the better. And I can usually more easily get a clustered/nonclustered change approved more easily if I don't have to suspend the data integrity rules, even for a few seconds. The attitude would be "so what if an app made a mistake at just that minute?" Whereas changing the cluster doesn't risk data integrity in the least if I am just dropping an index.