when i remove primary key constraint then SQL automatically remove the cluster index and
same for unique it will remove non-cluster index ?
views:
35answers:
2
+1
A:
Primary keys and other unique constraints are implemented using indexes which can be either clustered or non-clustered. If you remove the constraint you remove the index.
Matt Howells
2009-07-13 06:07:52
please read the question again
KuldipMCA
2009-07-13 06:22:13
I have read it several times, it's still as comprehensible as the first time I read it.
Matt Howells
2009-07-13 07:08:01
+1
A:
A table can have at most one clustered index. The clustered index is the table. The clustered index is removed (the table is turned into a heap) when the clustered index is dropped. The primary key is not necessary the clustered index, but it usually is. The unique constraint/index in question may or may not remove a clustered index depending whether is was or it was not a clustered index.
Updated:
I think I misred the question. If you drop a unique constraint, it will remove the corresponding non-clustred index, yes.
Remus Rusanu
2009-07-13 06:14:26
The primary key is *most* times the clustered key, and it will remove the clustered key when the constrain is dropped. But you have to verify if the clustered index is indeed the primary key or not to know for sure.
Remus Rusanu
2009-07-13 06:25:23