Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Primary Key Convert from Non-Cluster to Cluster IndexHow 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,
Post Follow-up to this message
Re: Primary Key Convert from Non-Cluster to Cluster IndexScript 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, > > > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster IndexI believe that you can go from nc to cl for a PK or UQ constraint using CREA TE 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 n o 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... > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster IndexAhh, 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 n o 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... > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster IndexIt 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... > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster IndexOn 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.
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster Index> 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, dro p the index, create the index and re-create the foreign keys. I doubt that EM is smart enough to exe cute 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@ 4a x.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. > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster IndexYour 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, d rop the index, create the > index and re-create the foreign keys. I doubt that EM is smart enough to e xecute 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 q0u1v 1l379f8ik3rmlu@4ax.com... > > >
Post Follow-up to this message Re: Primary Key Convert from Non-Cluster to Cluster Index> What is a good way to analysis tuning indexes? IMO, the brain. Know the datamodel, the data and work the query and executio n 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... > > Your correct, all the Index Tuning Wizard recommends is to drop all indexe s > except the cluster index. This is not much help. > > What is a good way to analysis tuning indexes? > > Thank You, > > > "Tibor Karaszi" wrote: >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
