February 15, 2012 at 1:53 am
Hi there,
I am currently in the process of checking all the indexes (in particular primary keys) and found a lot of primary keys that are not clustered indexes. I want to change all of that to clustered indexes but my database being relational I have a lot of foreign keys that blocks me from changing the primary keys. Now, my idea is to disable (not delete) the foreign key, change the primary key and enabling the foreign key again but I cannot seem to find a way to dis/enable the foreign key. I have looked high and low but maybe I am looking at the wrong places. Please help!!!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 15, 2012 at 3:05 am
Why would you always want your primary key to be clustered ??
You should first check if your table has a clustering index.
I would also pay attention to the composition of the primary key. Single column ? Datatype ? ...
Have a look at Clustered Index Design Guidelines at http://msdn.microsoft.com/en-us/library/ms190639.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2012 at 4:25 am
This project that I am doing has been coming with me ever since I inherited this database. I have been walking into walls in many ways because of a lack of knowledge. I was thrown into the deep side because I have not been a dba till I took over this database. It's not big, only +- 15 gb, but I have taken it from sluggish to fast. The first thing I learned was indexes because this database was so slow that the users battled through the day working on the system. I have already identified tables that I do not want or need to make clustered primary keys. What I am asking is:
1. Can this be done?
2. If yes, how?
Thank you
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 15, 2012 at 1:40 pm
maybe this attached sproc can get you started.
its goal is to generate a sequence of drop fk, convert pk 2 clustered, create fk encapsulated into a transaction per pk.
Actual modification to be run via copy/paste in a new query pane.
It served me well on a couple of occasions 😉
TEST it, test IT
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 16, 2012 at 6:42 am
ALZDBA (2/15/2012)
maybe this attached sproc can get you started.its goal is to generate a sequence of drop fk, convert pk 2 clustered, create fk encapsulated into a transaction per pk.
Actual modification to be run via copy/paste in a new query pane.
It served me well on a couple of occasions 😉
TEST it, test IT
ALZDBA,
Why does your script create constraints with the NOCHECK option? As far as I'm aware NOCHECK does nothing for PRIMARY KEY constraints but it makes FOREIGN KEY constraints untrusted, which is rarely a good idea and serves no obvious purpose here unless it's part of a larger process that re-CHECKs the keys. File group and other options associated with constraints are also removed and not recreated by the script. Be careful and definitely take out the NOCHECK keyword unless you are sure you want to do that.
February 16, 2012 at 7:27 am
Sorry for that.
Apparently I posted the wrong version.
Constraints should be using 'with check' for the obvious reasons.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 16, 2012 at 11:22 am
First consider there may be strong and compelling reasons to just keep the primary key on a natural non-clustered column(s) and have the table's clustered key on a timestamp or sequential identity.
If you do decide to cluster a primary key, consider writing a script to drop all foreign keys and then another script to re-create them. Once you drop and re-create the primary key, a foreign key has to be re-checked anyhow, assuming it's status were previously changed to no-check (disabled).
Also, altering your primary key from non-clustered to clustered will result in changes to the RID for all rows in the table and this a rebuild and possible fragmentation of any indexes on that table, so you might as well drop your indexes before altering the primary key, and then re-create the indexes too.
Consider this:
drop foreign keys
drop indexes
alter primary key to clustered
create indexes
create foreign keys
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 17, 2012 at 11:49 pm
Thanks guys for all the replies. I have actually decided that I am after all not to do it and rather check the tables as I go and where necessary do it one table at a time. I had a word with my boss about this and he does not think it is so serious to have unclustered pk's. I would however like to ask your opinion on something else on clustered/unclustered indexes.
I have noticed that some of my tables does not have clustered indexes at all including the pk. Should there not be at least one clustered index on a table? Just wondering. ALZDBA, I read up on that link you posted in your first comment and according to that clustered indexes helps the performance or am I wrong?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 18, 2012 at 6:32 am
Have a look how Grant summarizes it all:
http://www.sqlservercentral.com/Forums/FindPost684939.aspx
and here's another forum thread where I summed up my guidelines ( ... see how things match 😉 )
http://www.sqlservercentral.com/Forums/FindPost1033453.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply