Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
Craig
In SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>
|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>

Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>sql

Changing a clustered index - impact on queries

Hi All
I am trying to get an understanding of what the impact on queries is when
changing a clustered index on a table.
I understand that there will be an overall performance impact on the server,
but what I am trying to find out is what will happen to queries that perform
operations on the relevant table while this is happening. I am interested in
SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
We can assume that I will be using CREATE INDEX with DROP_EXISTING.
Thanks
CraigIn SQL 2000 changing the clustered index is an offline operation, which
means that the tables affected are under SCH_M lock and are not be
accessbile to queries during the time.
In SQL 2005 beta you will see a new feature called "online index build".
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
> server,
> but what I am trying to find out is what will happen to queries that
> perform
> operations on the relevant table while this is happening. I am interested
> in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>|||CB
First of all don't do that by EM , instead use query analizer. Yes it is a
good practice to create an ibdex with drop existing.
If you are interested what is actually going on during creation by EM , run
SQL Server Profiler and see. It will create a new table move the data ,
create an index and etd but all users will be locked while it does it.
If you are about crerate a new CI the do that the following
1) First of all drop all non-clusterd index
2) Drop a clustered index
3) Create CI .
4) Creat NCI
Note: Probably you will be better to deny access to users while you perfom
above operations
"CB" <craig.bryden@.derivco.com> wrote in message
news:%230S9Y1SKFHA.2880@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I am trying to get an understanding of what the impact on queries is when
> changing a clustered index on a table.
> I understand that there will be an overall performance impact on the
server,
> but what I am trying to find out is what will happen to queries that
perform
> operations on the relevant table while this is happening. I am interested
in
> SELECT, INSERT, UPDATE and DELETE (all of the basic operations).
> We can assume that I will be using CREATE INDEX with DROP_EXISTING.
> Thanks
> Craig
>

Change Tracking VS Update Index

When creating a new Table Schedule, there is an option for a Job Type of
"Update Index". I though this was Change Tracking, but it doesn't seem to
be. I still had to manually select Change Tracking from the full text menu.
What is Update Index?
Thanks
It is, but change tracking has two modes:
1) update index is a scheduled update, ie you can update the index on demand
or as a scheduled job
2) update index in background - ie have it happen continuously
"Don Schilling" <fake@.ReplyToGroup.com> wrote in message
news:OMa3r$2PEHA.648@.TK2MSFTNGP10.phx.gbl...
> When creating a new Table Schedule, there is an option for a Job Type of
> "Update Index". I though this was Change Tracking, but it doesn't seem to
> be. I still had to manually select Change Tracking from the full text
menu.
> What is Update Index?
> Thanks

Change TinyInt identity into SmallInt

Hi,
I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
change the column to a SmallInt.
I tried this:
drop index behaviorprop.PK_BehaviorProp
alter table behaviorprop alter column BP_ID smallint
but I get
An explicit DROP INDEX is not allowed on index
'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
constraint enforcement.
How can I remove the primary key constraint and add it again after the
change?
Thanks in advance,
Stijn Verrept.You need to drop the constraint instead...
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Hi
create table test (c1 tinyint identity(1,1) not null)
insert into test default values
alter table test alter column c1 int
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Tony Rogerson wrote:

> You need to drop the constraint instead...
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key
> ( bp_id )
> commit tran
Thanks for your reply, but then I get:
'PK_Behaviourprop' is not a constraint.
and when I look in enterprise manager at constraints there aren't any
there, only the primary key (in the indexes tab).
Kind regards,
Stijn Verrept.|||Oh, I did not care about PRIMARY KEY constraint , see Tony's answer
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ucjy$rMeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Hi
> create table test (c1 tinyint identity(1,1) not null)
> insert into test default values
> alter table test alter column c1 int
>
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
>|||Need your version, use PRINT @.@.VERSION, the CREATE TABLE script including
indexes and any constraints.
And, the exact error message (cut and paste).
many thanks
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:SI-dnbH15pMVBvTZRVnyvQ@.scarlet.biz...
> Tony Rogerson wrote:
>
> Thanks for your reply, but then I get:
> 'PK_Behaviourprop' is not a constraint.
> and when I look in enterprise manager at constraints there aren't any
> there, only the primary key (in the indexes tab).
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Need your version, use PRINT @.@.VERSION, the CREATE TABLE script
> including indexes and any constraints.
> And, the exact error message (cut and paste).
> many thanks
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Error message:
Server: Msg 3728, Level 16, State 1, Line 2
'PK_Behaviourprop' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors.
The create table script is at: http://www.entrysoft.com/script.sql it
will create temptest database and will display the error when you try
to run
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
It must be something simple.
Thanks for taking the time!
Kind regards,
Stijn Verrept.|||Not really answering the question but this script will work.... Good (new)
sql 2005 management studio and the create change script option on modify
table ;)
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Users
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Seniors
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BehaviorProp
(
BP_ID smallint NOT NULL IDENTITY (1, 1),
BP_SNID int NOT NULL,
BP_Stamp smalldatetime NOT NULL,
BP_Score1 tinyint NOT NULL,
BP_Score2 tinyint NOT NULL,
BP_Score3 tinyint NOT NULL,
BP_Score4 tinyint NOT NULL,
BP_Score5 tinyint NOT NULL,
BP_Score6 tinyint NOT NULL,
BP_Desc1 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc2 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc3 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc4 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc5 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc6 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_USID smallint NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp ON
GO
IF EXISTS(SELECT * FROM dbo.BehaviorProp)
EXEC('INSERT INTO dbo.Tmp_BehaviorProp (BP_ID, BP_SNID, BP_Stamp,
BP_Score1, BP_Score2, BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1,
BP_Desc2, BP_Desc3, BP_Desc4, BP_Desc5, BP_Desc6, BP_USID)
SELECT CONVERT(smallint, BP_ID), BP_SNID, BP_Stamp, BP_Score1, BP_Score2,
BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1, BP_Desc2, BP_Desc3,
BP_Desc4, BP_Desc5, BP_Desc6, BP_USID FROM dbo.BehaviorProp WITH (HOLDLOCK
TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp OFF
GO
DROP TABLE dbo.BehaviorProp
GO
EXECUTE sp_rename N'dbo.Tmp_BehaviorProp', N'BehaviorProp', 'OBJECT'
GO
ALTER TABLE dbo.BehaviorProp ADD CONSTRAINT
PK_BehaviorProp PRIMARY KEY CLUSTERED
(
BP_ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Seniors FOREIGN KEY
(
BP_SNID
) REFERENCES dbo.Seniors
(
SN_ID
) ON DELETE CASCADE
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Users FOREIGN KEY
(
BP_USID
) REFERENCES dbo.Users
(
US_ID
) ON DELETE CASCADE
GO
COMMIT
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:Ke-dnSqDzbrrO_TZRVny2w@.scarlet.biz...
> Tony Rogerson wrote:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> Error message:
> Server: Msg 3728, Level 16, State 1, Line 2
> 'PK_Behaviourprop' is not a constraint.
> Server: Msg 3727, Level 16, State 1, Line 2
> Could not drop constraint. See previous errors.
> The create table script is at: http://www.entrysoft.com/script.sql it
> will create temptest database and will display the error when you try
> to run
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key (
> bp_id )
> commit tran
>
> It must be something simple.
> Thanks for taking the time!
>
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Not really answering the question but this script will work.... Good
> (new) sql 2005 management studio and the create change script option
> on modify table ;)
This works indeed! Thanks!
Kind regards,
Stijn Verrept.

change primary index value

Is there any way to change the value of a primary key value?Is there any way to change the value of a primary key value?

Use cascading Update to change a primary key and its related foreign key.|||is cascading update slow?

It just dawned on me that if I changed a primary key I would have to change every PK in all rows.

Will there be a performance hit if this is done?|||in all rows of which table? you wouldn't need to do it if you're talking about ON UPDATE CASCADE

UPDATE takes as long as UPDATE takes, but if there are cascading updates, those will obviously take time too

could you give a more specific example, please|||OK for example:

PK Product Price
1 SQL Book $99.99
2 ASP Book $89.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00

For example, asp book has matched one of my queries and I want to switch ASP book and SQL book PKs. So the table now looks like this:

PK Product Price
1 ASP Book $89.99
2 SQL Book $99.99
3 C# Intro $99.99
4 Computer $500.00
5 Hard Drive $150.00

is this possible?|||i suppose it's possible, but by far the more important question is: why would you want to do this!!!!

no offence, but do you understand what the purpose of the PK is?|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.|||use ORDER BY if you want a specific sequence

manipulating a numeric surrogate primary key to maintain a sequence is like trying to empty a swimming pool with a teaspoon

add an index to the table on the price column, and ORDER BY will be efficient|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.|||well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.
SORT BY?
You mean ORDER BY,I don't think that using ORDER BY will give you a performance hit if you have indices on those fields,well in that case an clustered index can help you,if you want to keep the order in which the data is inserted on that PK.
Creating proper Indices helps a lot in the query performance...|||So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.

5000 rows is NOTHING.

if you put an index on the column you want to order by, you'll be fine.

also, nobody said that your PK needs to be clustered. If you are that concerned about perf of order by, make the PK nonclustered, and the index on your sorting column clustered, as rudra suggests.

EDIT: generally, I have found that very often it's wrong to make assumptions about where perf problems are. The only to know for sure is to measure. For sql this means use profiler and showplan. For compiled code it means profile it using some other tool. The point is you need to measure before making statements about where the perf issues lie.

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.

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.

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.

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.