specifying the different file group on the statement?
Basically, I need to
1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right?
Any simpler approach?This is a multi-part message in MIME format.
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING =option.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
can I do it by just recreating the clustered index by specifying the different file group on the statement?
Basically, I need to 1) drop all nonclustered index
2) drop the clustered index
3) create the clustered index in different group
4) create all the nonclustered index in differeng group
Right? Any simpler approach?
--=_NextPart_000_04DF_01C377A2.8F3183A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Steps 2 and 3 can be merged by =creating the index with the DROP_EXISTING option.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_04DF_01C377A2.8F3183A0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't have to drop the reference. Consider the following example:
create table t1
(
id int constraint PK_t1 primary key
)
create table t2
(
id int references t1
)
go
create unique clustered index PK_t1 on t1 (id) with drop_existing
go
drop table t2, t1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
Thanks, Tom.
If the table is referenced by any other table, I will
have to drop the reference and then recreate it right?
Because I have to move the primary key index to different
file group too.
>--Original Message--
>Steps 2 and 3 can be merged by creating the index with the DROP_EXISTING option.
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>can I do it by just recreating the clustered index by >specifying the different file group on the statement?
>Basically, I need to >1) drop all nonclustered index
>2) drop the clustered index
>3) create the clustered index in different group
>4) create all the nonclustered index in differeng group
>Right? >Any simpler approach?
--=_NextPart_000_0521_01C377A5.C1E5ADF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You don't have to drop the =reference. Consider the following example:
create table t1( id int constraint PK_t1 primary =key)
create table t2( id int references t1)go
create unique clustered index =PK_t1 on t1 (id) with drop_existinggo
drop table t2, =t1
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_0521_01C377A5.C1E5ADF0--|||Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different
group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the
following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with
drop_existing
>go
>drop table t2, t1
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message
news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with
>the DROP_EXISTING option.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message
>news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by
>>specifying the different file group on the statement?
>>Basically, I need to
>>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right?
>>Any simpler approach?|||This is a multi-part message in MIME format.
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just following up, if you want to move the text/image data, you will =have to drop and recreate.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebEjUG9dDHA.1880@.TK2MSFTNGP10.phx.gbl...
You can use DROP_EXISTING on the PK:
create table t1
(
id int constraint PK_t1 primary key nonclustered
,junk char (5) not null
)
create clustered index idx on t1 (junk)
go
create table t2
(
id int references t1
)
go
create clustered index idx on t1 (junk)
with drop_existing
go
create unique nonclustered index PK_t1 on t1 (id)
with drop_existing
go
drop table t2, t1
As for text/image, I'll have to look further.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"JZHU" <hzhua16@.hotmail.com> wrote in message =news:0d7701c377cd$ebd17680$a601280a@.phx.gbl...
Thanks, Tom.
But I can not use drop existing on the primary key index
because it is not clustered. If I do so, the recreate
clustered index will recreate the primary key index too.
It's a waste of time because I have to recreate
the primary key index again to move it to a different group. Looks like it's a trade off here, right?
One more question ( I hope it is the last one:):
If the table has field text/image which need to move
to the different filegroup too, how can I do it?
>--Original Message--
>You don't have to drop the reference. Consider the following example:
>create table t1
>(
> id int constraint PK_t1 primary key
>)
>create table t2
>(
> id int references t1
>)
>go
>create unique clustered index PK_t1 on t1 (id) with drop_existing
>go
>drop table t2, t1
>
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"JZHU" <hzhua16@.hotmail.com> wrote in message news:015d01c377c5$b48e1960$a401280a@.phx.gbl...
>Thanks, Tom.
>If the table is referenced by any other table, I will
>have to drop the reference and then recreate it right?
>Because I have to move the primary key index to different
>file group too.
>>--Original Message--
>>Steps 2 and 3 can be merged by creating the index with >the DROP_EXISTING option.
>>-- >>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"JZHU" <hzhua16@.hotmail.com> wrote in message >news:66cf01c377c3$66886a10$a501280a@.phx.gbl...
>>can I do it by just recreating the clustered index by >>specifying the different file group on the statement?
>>Basically, I need to >>1) drop all nonclustered index
>>2) drop the clustered index
>>3) create the clustered index in different group
>>4) create all the nonclustered index in differeng group
>>Right? >>Any simpler approach?
--=_NextPart_000_0026_01C3783F.CB8BDEC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Just following up, if you want to move =the text/image data, you will have to drop and recreate.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau"
You can use DROP_EXISTING on the =PK:
create table =t1( id int constraint PK_t1 primary key nonclustered,junk char (5) not null)create clustered index idx on t1 (junk)go
create table =t2( id int references t1)go
create clustered index idx on =t1 (junk)with drop_existinggocreate unique nonclustered index =PK_t1 on t1 (id)with drop_existinggo
drop table t2, =t1
As for text/image, I'll have to look further.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"JZHU"
--=_NextPart_000_0026_01C3783F.CB8BDEC0--
