February 4, 2011 at 6:53 am
Hi,
We have a very large table on which there is no PK. For some reason there is a clustered index on the table defined on the columns which should be in the PK.
We have now realized that we should have a PK on the table defined on the columns. If we add the PK, we should first drop the existing index. When the PK will be added, the clustered index will be created again.
What we would prefer is a way to tell SQL2K8 to add the PK but while doing so do not create a clustered index, rather use the one that exists.
Is there a way to do so?
Thanks,
Yash
February 4, 2011 at 8:11 am
Yash Ganthe (2/4/2011)
We have a very large table on which there is no PK. For some reason there is a clustered index on the table defined on the columns which should be in the PK.We have now realized that we should have a PK on the table defined on the columns. If we add the PK, we should first drop the existing index. When the PK will be added, the clustered index will be created again.
What we would prefer is a way to tell SQL2K8 to add the PK but while doing so do not create a clustered index, rather use the one that exists.
alter table add primary key using index
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 5, 2011 at 9:36 pm
ADD PK USING INDEX is not supported in SQL Server.
This is an Oracle feature.
February 6, 2011 at 5:49 am
I fear you cannot do that with sqlserver.
- a primary key is a constraint that enforces uniqueness for the column(s) combination. it is materialized by an actual index. If your table lacks a clustering index, your PK will be created as such, unless you specify "nonclustering".
- if you also have other indexes defined, you may want to disable them before your actions and rebuild them afterward, so you have control over the action.
- If your current clustering index matches your PK to be defined, there is no other option than to drop the current clustering index and create your PK constraint and have it defined clustering. ( you cannot use the "drop existing clause as with regular indexes)
To avoid uniqueness problems, you may want to perform these actions in a single transaction. (encapsulate your statements in a "begin tran ... commit/rollback block)
Keep in mind this kind of action will write your data twice !
Once to convert your clustering index into a heap containing your data, once to create the new clustering index (PK).
Double check your data files as well as your log files sizes, so they are able to cope with the load.
If - as you stated - your table is huge, your consumers will notice your action, so request for downtime !!
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 and code to get the best help
- 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 6, 2011 at 9:39 am
Yash Ganthe (2/5/2011)
ADD PK USING INDEX is not supported in SQL Server.This is an Oracle feature.
You are absolutely correct - I shouldn't be posting on Friday afternoon 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply