Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,
Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh
|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
sql
Showing posts with label non-cluster. Show all posts
Showing posts with label non-cluster. Show all posts
Wednesday, March 21, 2012
Primary Key Non-Cluster Index
Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance o
f
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance
of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance o
f
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance
of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
Primary Key Non-Cluster Index
Is there a case when the primary key should not be cluster index?
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
I have a SQL Server 2000 database that has several tables that have
non-cluster indexes for primary keys. Will this slow down the performance of
application that uses this table often?
Thank You,Joe,
1. Yes. When the clustered index is better utilized on a different column
to support range and sorting queries.
2. It depends - on the type of queries that are being used in the
application.
HTH
Jerry
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,|||On Tue, 25 Oct 2005 11:16:11 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>Is there a case when the primary key should not be cluster index?
>I have a SQL Server 2000 database that has several tables that have
>non-cluster indexes for primary keys. Will this slow down the performance of
>application that uses this table often?
If there is NO clustered index on a table, and if you delete rows
(including if you update rows and because they need more space they
have to move), then you may wake up the (potentially evil) SQLServer
ghost.
Otherwise, whether a clustered index is going to help, or hurt, or do
nothing is highly dependent on application logic.
Josh|||"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:C3414875-894D-4F0E-87DE-46CA9A842CD8@.microsoft.com...
> Is there a case when the primary key should not be cluster index?
> I have a SQL Server 2000 database that has several tables that have
> non-cluster indexes for primary keys. Will this slow down the performance
> of
> application that uses this table often?
> Thank You,
Of course. Keep in mind that a clustered index sorts the actual data rows
in index order. It is most often used when you are selecting on a range of
data like "OrderNumber Between 10 and 100", or you are sorting your data in
clustered index order.
If you are doing a lot of INSERTs into the table, the data must be placed in
clustered index order. If you are using a monotonically increasing value
(like IDENTITY), then there will always be space at the end of the last
datapage for your next row of data and page splitting doesn't occur. If you
are not using a monotonically increasing value, for example, LastName or a
GUID, then the database will have to make space for the new row if there is
not space already. This can cause page splitting which is time consuming
and resource consuming.
The performance of other applications depends on what those applications are
querying for.
Rick Sawtell
MCT, MCSD, MCDBA
Tuesday, March 20, 2012
Primary Key Convert from Non-Cluster to Cluster Index
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,
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@.microsoft.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,
>
>
|||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@.microsoft.com...
>
|||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@.microsoft.com...
>
|||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@.hotmail.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...
>
|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.
|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>
|||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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>
>
|||> 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@.microsoft.com...[vbcol=seagreen]
> 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:
index to a
cluster index?
Please help me complete this task.
Thank You,
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@.microsoft.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,
>
>
|||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@.microsoft.com...
>
|||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@.microsoft.com...
>
|||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@.hotmail.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...
>
|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.
|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>
|||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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>
>
|||> 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@.microsoft.com...[vbcol=seagreen]
> 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:
Primary Key Convert from Non-Cluster to Cluster Index
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,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@.microsoft.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,
>
>|||I 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@.microsoft.com...
>|||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 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@.microsoft.com...
>|||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@.hotmail.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...
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.
4ax
.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>|||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, 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:5ck4o1te08umautq0u1v
1l379f8ik3rmlu@.4ax.com...
>
>|||> 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@.microsoft.com...[vbcol=seagreen]
> 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:
>
index to a
cluster index?
Please help me complete this task.
Thank You,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@.microsoft.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,
>
>|||I 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@.microsoft.com...
>|||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 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@.microsoft.com...
>|||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@.hotmail.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...
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.
4ax
.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>|||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, 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:5ck4o1te08umautq0u1v
1l379f8ik3rmlu@.4ax.com...
>
>|||> 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@.microsoft.com...[vbcol=seagreen]
> 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:
>
Primary Key Convert from Non-Cluster to Cluster Index
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,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@.microsoft.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,
>
>|||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@.microsoft.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,
>>
>>
>|||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@.microsoft.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,
>>
>>
>|||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@.hotmail.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...
>> 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@.microsoft.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,
>>
>>
>>
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.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.
>|||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:
> > 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.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.
> >
> >
>
>|||> 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@.microsoft.com...
> 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:
>> > 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
>> > <tibor_please.no.email_karaszi@.hotmail.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.
>> >
>> >
>>
index to a
cluster index?
Please help me complete this task.
Thank You,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@.microsoft.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,
>
>|||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@.microsoft.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,
>>
>>
>|||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@.microsoft.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,
>>
>>
>|||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@.hotmail.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...
>> 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@.microsoft.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,
>>
>>
>>
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.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.|||> 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.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.
>|||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:
> > 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.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.
> >
> >
>
>|||> 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@.microsoft.com...
> 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:
>> > 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:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
>> > <tibor_please.no.email_karaszi@.hotmail.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.
>> >
>> >
>>
Subscribe to:
Posts (Atom)