|
Home > Archive > MS SQL Server > November 2005 > Primary Key Convert from Non-Cluster to Cluster Index
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Primary Key Convert from Non-Cluster to Cluster Index
|
|
| Joe K. 2005-11-17, 8:23 pm |
|
How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,
| |
| Geoff N. Hiten 2005-11-17, 8:23 pm |
| Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286- 9C15B91870E7@microso
ft.com...
>
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
>
> Please help me complete this task.
>
> Thank You,
>
>
>
>
| |
| Tibor Karaszi 2005-11-17, 8:23 pm |
| I believe that you can go from nc to cl for a PK or UQ constraint using CREATE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Geoff N. Hiten" <sqlcraftsman@gmail.com> wrote in message
news:uAz8e966FHA.3588@TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
>
> Test this at least twice on a development/test environment.
>
> I recently did this on a table with 26 foreign key references. There is no short cut.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Joe K." <Joe K.@discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286- 9C15B91870E7@microso
ft.com...
>
>
| |
| Tibor Karaszi 2005-11-17, 8:23 pm |
| Ahh, come to think about it, the FK's most probably have to be dropped even when using
DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Geoff N. Hiten" <sqlcraftsman@gmail.com> wrote in message
news:uAz8e966FHA.3588@TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
>
> Test this at least twice on a development/test environment.
>
> I recently did this on a table with 26 foreign key references. There is no short cut.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Joe K." <Joe K.@discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286- 9C15B91870E7@microso
ft.com...
>
>
| |
| Geoff N. Hiten 2005-11-18, 7:23 am |
| It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:umz77K86FHA.2364@TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Geoff N. Hiten" <sqlcraftsman@gmail.com> wrote in message
> news:uAz8e966FHA.3588@TK2MSFTNGP15.phx.gbl...
>
>
| |
| jxstern 2005-11-21, 8:23 pm |
| On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.
| |
| Tibor Karaszi 2005-11-22, 7:23 am |
| > But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
WITH DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"jxstern" <jxstern@nowhere.xyz> wrote in message news:5ck4o1te08umaut
q0u1v1l379f8ik3rmlu@
4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote:
>
> But won't the EM do all the voodoo for you?
>
> J.
>
>
| |
| Joe K. 2005-11-28, 8:23 pm |
|
Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:
>
> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
> WITH DROP_EXISTING.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "jxstern" <jxstern@nowhere.xyz> wrote in message news:5ck4o1te08umaut
q0u1v1l379f8ik3rmlu@
4ax.com...
>
>
>
| |
| Tibor Karaszi 2005-11-29, 8:23 pm |
| > What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and execution plan. ITW can be some
input, but not a replacement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890- 261FCE34A769@microso
ft.com...[color=darkred]
>
> Your correct, all the Index Tuning Wizard recommends is to drop all indexes
> except the cluster index. This is not much help.
>
> What is a good way to analysis tuning indexes?
>
> Thank You,
>
>
> "Tibor Karaszi" wrote:
>
|
|
|
|
|