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.
| Author |
Topic |
|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 2012-02-05 : 17:39:10
|
| Hi AllI have in my database a table that has a primary key that was set by mistake to be a noncluster and I have a lot of foreign key in other tables pointint to this primary key.If I changed now its type from noncluster to cluster will this affect the other tables will this affect my data Thankssarah |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-05 : 18:16:08
|
| To make that change will require dropping the foreign keys and the primary key, then re-creating them after the primary key is rebuilt as a clustered index. During the time the constraints are dropped you could insert or update data that would cause subsequent violations. The best way to do this is to shut down any applications that can do such operations, or find the quietest time to make the change. |
 |
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-05 : 18:41:59
|
| What you mean by the followingDuring the time the constraints are dropped you could insert or update data that would cause subsequent violations. If i shut down the system no one will be using it and dropped all the foreign keys referencing this primary key and dropped this primary key and then recreated the primary key as cluster and recreated all the foreign keys will that workAnother thing is the fragmentation high because my mistake the primary key was set to be a noncluster onea lot of the composite keys that consist of this primary key and another one also are highly fragmentedis all that because the primary key was set by mistake to be non clusteredsarah |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-05 : 19:17:47
|
quote:
If i shut down the system no one will be using it and dropped all the foreign keys referencing this primary key and dropped this primary key and then recreated the primary key as cluster and recreated all the foreign keys will that work
Yes, as I said this is the safest option.Keep in mind that clustered/non-clustered indexes are separate things from the primary key. If you create or drop a clustered index it will rebuild all non-clustered indexes on the table. This rebuild should also correct the fragmentation of the non-clustered indexes. If not, you can run ALTER INDEX...REORGANIZE on your indexes to defragment them. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 20:26:44
|
quote:
Originally posted by sarahmfr
Another thing is the fragmentation high because my mistake the primary key was set to be a noncluster onea lot of the composite keys that consist of this primary key and another one also are highly fragmentedis all that because the primary key was set by mistake to be non clustered
That won't be the cause of fragmentation.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-06 : 05:24:53
|
| He's also posted all 3 over at SSC.--Gail ShawSQL Server MVP |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-07 : 12:55:20
|
| Thanks a lotsarah |
|
|
|
|
|
|
changing a pk set as noncluster to cluster