Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Showing posts with label non-clustered. Show all posts
Showing posts with label non-clustered. Show all posts
Friday, February 10, 2012
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?
I think I found the answer, you do a create index with drop existing.
|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegro ups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>
|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
Change non-clustered index to clustered
Hi,
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
When one of our tables was created, the primary key was created as a
non-clustered index. I would like to change this to be clustered but
the table is in replication so it can't be dropped and recreated as
such. In books online I read this passage:
"A nonclustered index can be converted to a clustered index type by
specifying CLUSTERED in the index definition. This operation must be
performed with the ONLINE option set to OFF. Conversion from clustered
to nonclustered is not supported regardless of the ONLINE setting."
So it sounds like it can be done, but I can't see how in the syntax
definition. Has anyone done this on a 2005 box?I think I found the answer, you do a create index with drop existing.|||You have to drop it, but if you are using transactional replication I don't
believe you can do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1158679375.078010.48050@.k70g2000cwa.googlegroups.com...
> Hi,
> When one of our tables was created, the primary key was created as a
> non-clustered index. I would like to change this to be clustered but
> the table is in replication so it can't be dropped and recreated as
> such. In books online I read this passage:
> "A nonclustered index can be converted to a clustered index type by
> specifying CLUSTERED in the index definition. This operation must be
> performed with the ONLINE option set to OFF. Conversion from clustered
> to nonclustered is not supported regardless of the ONLINE setting."
> So it sounds like it can be done, but I can't see how in the syntax
> definition. Has anyone done this on a 2005 box?
>|||Yes, it looks like I am going to have to drop the publication tonight,
make my schema changes, and then recreate the publication with a script.
change nc-index to c-index?
Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?
thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:
update facts
set name = value
where facts_id = @.facts_id
so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.
I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.
thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:
update facts
set name = value
where facts_id = @.facts_id
so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.
I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.
Subscribe to:
Posts (Atom)