moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,
Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>
|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks Waldo
If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
If the table already has a clustered index, you can use the CREATE INDEX
command's WITH DROP_EXISTING clause to recreate
the clustered index and move it to a particular filegroup. When a table has
a clustered index, the leaf level of the index and the data pages of the
table essentially become one and the same. The table must exist where the
clustered index exists, so if you create or recreate a clustered
index-placing the index on a particular filegroup-you're moving the table to
the new filegroup as well.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
If the table already has a clustered index, you can use the CREATE INDEX
command's WITH DROP_EXISTING clause to recreate
the clustered index and move it to a particular filegroup. When a table has
a clustered index, the leaf level of the index and the data pages of the
table essentially become one and the same. The table must exist where the
clustered index exists, so if you create or recreate a clustered
index-placing the index on a particular filegroup-you're moving the table to
the new filegroup as well.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
If the table already has a clustered index, you can use the CREATE INDEX
command's WITH DROP_EXISTING clause to recreate
the clustered index and move it to a particular filegroup. When a table has
a clustered index, the leaf level of the index and the data pages of the
table essentially become one and the same. The table must exist where the
clustered index exists, so if you create or recreate a clustered
index-placing the index on a particular filegroup-you're moving the table to
the new filegroup as well.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>> Hi
>> I want to move a table to another filegroup using T-SQL (alter table
>> ...).
>> In BOL i do not find an example of this case. I trried something like
>> alter <table>
>> move to <filegroup>
>> but it does not work
>> Can somebody help me please. Thanks Waldo
>sql

moving a heap to another filegroup

I have a big table (heap)... well, not so big, I have a small server
and I want to spread access to it across several new disks dedicated
only to that table.

I known its possible to do that creating a clustered index with "ON
filegroup" option but I want to maintain it as a heap, is there any
way to do this without dropping indexes/references - bulk unload -
create table - bulk load - create indexes?."el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407291227.11f42272@.posting.google.c om...
> I have a big table (heap)... well, not so big, I have a small server
> and I want to spread access to it across several new disks dedicated
> only to that table.
> I known its possible to do that creating a clustered index with "ON
> filegroup" option but I want to maintain it as a heap, is there any
> way to do this without dropping indexes/references - bulk unload -
> create table - bulk load - create indexes?.

As far as I know, you have to drop everything and recreate it, as you have
described. But in general, in MSSQL it's a good idea to have a clustered
index on all tables, so it would be interesting to know why you prefer to
maintain a heap table.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<410999b5$1_3@.news.bluewin.ch>...
> "el emperador" <1492a2001@.terra.es> wrote in message
> news:dc979468.0407291227.11f42272@.posting.google.c om...
> > I have a big table (heap)... well, not so big, I have a small server
> > and I want to spread access to it across several new disks dedicated
> > only to that table.
> > I known its possible to do that creating a clustered index with "ON
> > filegroup" option but I want to maintain it as a heap, is there any
> > way to do this without dropping indexes/references - bulk unload -
> > create table - bulk load - create indexes?.
> As far as I know, you have to drop everything and recreate it, as you have
> described. But in general, in MSSQL it's a good idea to have a clustered
> index on all tables, so it would be interesting to know why you prefer to

I agree.

> maintain a heap table.

Well, I have found that after define a clustered index some queries
changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
Lookup" to a "Clustered Index Seek") and surprisingly got a little
slower, that's suggar for the query optimizer so it thinks its better
to use it, I have to investigate why this happens before define "the
correct" clustered index, a not easy task as you know. Right now I
only want to improve "bookmark lookup" on this table that is 60%-80%
of the weigth of the queries.

> Simon

Thanks|||Hi

Adding the clustered index in the new filegroup and then dropping it will
leave the data in the new filegroup. Your other indexes will be rebuilt
twice though, so it may be quicker to drop and re-create them yourself.

John

"el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407301415.28c2c4@.posting.google.com ...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
news:<410999b5$1_3@.news.bluewin.ch>...
> > "el emperador" <1492a2001@.terra.es> wrote in message
> > news:dc979468.0407291227.11f42272@.posting.google.c om...
> > > I have a big table (heap)... well, not so big, I have a small server
> > > and I want to spread access to it across several new disks dedicated
> > > only to that table.
> > > > I known its possible to do that creating a clustered index with "ON
> > > filegroup" option but I want to maintain it as a heap, is there any
> > > way to do this without dropping indexes/references - bulk unload -
> > > create table - bulk load - create indexes?.
> > As far as I know, you have to drop everything and recreate it, as you
have
> > described. But in general, in MSSQL it's a good idea to have a clustered
> > index on all tables, so it would be interesting to know why you prefer
to
> I agree.
> > maintain a heap table.
> Well, I have found that after define a clustered index some queries
> changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
> Lookup" to a "Clustered Index Seek") and surprisingly got a little
> slower, that's suggar for the query optimizer so it thinks its better
> to use it, I have to investigate why this happens before define "the
> correct" clustered index, a not easy task as you know. Right now I
> only want to improve "bookmark lookup" on this table that is 60%-80%
> of the weigth of the queries.
> > Simon
> Thanks|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<dBPOc.12029$vm4.122553507@.news-text.cableinet.net>...
> Hi
> Adding the clustered index in the new filegroup and then dropping it will
> leave the data in the new filegroup. Your other indexes will be rebuilt
> twice though, so it may be quicker to drop and re-create them yourself.
> John

Thanks John, that's perfect.

Move Table to other filegroup?

Hi every body,
Is there anyone know the way to move a table with data to other file group?Use 'Alter Database' statement. You may have to do the 'Alter' to add new filegroups, then do the moving tables and cleaning up old filegroups. To remove it, of course, it's another 'Alter'. Remember to remove a filegroup the file has to be empty. Good luck!|||I believe that you will have to:

1. Add the new file group
2. Create an identical table on the new file group with a different name
3. Copy the data from the existing table to the new table
4. Drop the old table
5. Rename the new table

If you want to retain indexes, constraints, foreign keys and defaults with the same names, then you will have to re-create these after dropping the current ones.

Regards,

hmscott|||Actually, you can virtually move the data from the table to a different filegroup by creating a clustered index and placing it wherever you want.

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.
Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>
sql

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

Move Table to different filegroup.

Hi everyone,
We have a production database holding time based manufacturing data. A new
table is created on the 1st day of each month and data is saved there until
the 1st day of the next month.
Each new table holds one month's worth of data - table size is between 2.5
GB and 4 GB. For the purpose of more efficient backup strategy we want to
move tables older than 1 year to a different
filegroup. Moving a table table using Enterpise Manager takes a long time
and causes the transaction log to grow by about 7-8 GB even if I set the
Recovery model to Simple.
What can I do to be able to move the tables more quickly and efficiently ?
Best regards.Hi
If you the table has a clustered index , just re-create an index on
different file group . It is just because a CI in leaf level has actual
data and as result all table will be moved as well
--Create a table via QA and not via EM
CREATE TABLE mywind..t1 (id int) ON new_customers
CREATE TABLE mywind..t2 (id int) ON sales
GO
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:u8ma5BATGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi everyone,
> We have a production database holding time based manufacturing data. A new
> table is created on the 1st day of each month and data is saved there
> until the 1st day of the next month.
> Each new table holds one month's worth of data - table size is between 2.5
> GB and 4 GB. For the purpose of more efficient backup strategy we want to
> move tables older than 1 year to a different
> filegroup. Moving a table table using Enterpise Manager takes a long time
> and causes the transaction log to grow by about 7-8 GB even if I set the
> Recovery model to Simple.
> What can I do to be able to move the tables more quickly and efficiently ?
>
> Best regards.
>
>

move table to another filegroup

alter table db.schme.table drop move to filegroup1?

this doesnot work

thanks

your syntax looks wrong

try:

altertable dbo.Gladiators

dropconstraint pk_Gladiators_int1

WITH(moveto'filegroup1')

|||

In order to move a table to another filegroup, use the statement "create index" with the option "with drop_existing", to recreate the clustered index, and specify the new filegroup. See "create index" in BOL.

AMB

move table that has no index from one Filegroup to another

Is there a better way to move a huge table that has no index on it from one
Filegroup to another ? Using SQL 2000The best way is to create a clustered index. Otherwise, you have to create a
new table, copy all of the data, drop the old table, rename the new one, and
I did not even mention any referential integrity issues.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> Is there a better way to move a huge table that has no index on it from
one
> Filegroup to another ? Using SQL 2000
>|||And what if you had a clustered index created that had references to it ?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> The best way is to create a clustered index. Otherwise, you have to create
a
> new table, copy all of the data, drop the old table, rename the new one,
and
> I did not even mention any referential integrity issues.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > Is there a better way to move a huge table that has no index on it from
> one
> > Filegroup to another ? Using SQL 2000
> >
> >
>|||Well, the table is avaiable for reading, but not for writing during the
index rebuild, thus the RI is maintained.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u#At6p$VDHA.1280@.tk2msftngp13.phx.gbl...
> And what if you had a clustered index created that had references to it ?
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uxzo5T$VDHA.2360@.TK2MSFTNGP12.phx.gbl...
> > The best way is to create a clustered index. Otherwise, you have to
create
> a
> > new table, copy all of the data, drop the old table, rename the new one,
> and
> > I did not even mention any referential integrity issues.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:#MS1ZX#VDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > > Is there a better way to move a huge table that has no index on it
from
> > one
> > > Filegroup to another ? Using SQL 2000
> > >
> > >
> >
> >
>

move table data from one filegroup to another

hi,
i wanna move table data from one filegroup to another , i
can do it in Enterprise Manager . can i use script to run
it in SQL Analyzer ?
Your answer is highly apprieated .(Re)-create a/the clustered index on the table.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"rainbow" <genghj@.routon.com> wrote in message news:02f101c3871c$05237630$a001280a@.phx.gbl...
> hi,
> i wanna move table data from one filegroup to another , i
> can do it in Enterprise Manager . can i use script to run
> it in SQL Analyzer ?
> Your answer is highly apprieated .|||What if they dont have a clustered index ..a heap or what if they just have
only non clustered indexes.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OUkjT5xhDHA.1952@.TK2MSFTNGP10.phx.gbl...
> (Re)-create a/the clustered index on the table.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "rainbow" <genghj@.routon.com> wrote in message
news:02f101c3871c$05237630$a001280a@.phx.gbl...
> > hi,
> > i wanna move table data from one filegroup to another , i
> > can do it in Enterprise Manager . can i use script to run
> > it in SQL Analyzer ?
> > Your answer is highly apprieated .
>|||That's why Tibor put (RE)Create
It means recreate the clustered index if you have one, or create one if you
don't. You can drop the clustered index afterward if you really don't want
to keep it, but you should really have a good reason NOT to have a clustered
index on a table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u6a#ep3hDHA.1200@.TK2MSFTNGP09.phx.gbl...
> What if they dont have a clustered index ..a heap or what if they just
have
> only non clustered indexes.
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OUkjT5xhDHA.1952@.TK2MSFTNGP10.phx.gbl...
> > (Re)-create a/the clustered index on the table.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "rainbow" <genghj@.routon.com> wrote in message
> news:02f101c3871c$05237630$a001280a@.phx.gbl...
> > > hi,
> > > i wanna move table data from one filegroup to another , i
> > > can do it in Enterprise Manager . can i use script to run
> > > it in SQL Analyzer ?
> > > Your answer is highly apprieated .
> >
> >
>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
Regards,
Jamie
I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
Regards,
Jamie
"Tibor Karaszi" wrote:

> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH =1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migrate
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH => 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> > If a new filegroup is created on a new disk drive, is there a way to migrate
> > a specific clustered index to the new filegroup?
> > --
> > Regards,
> > Jamie
>|||Try adding UNIQUE to the index definition, since it comes from a primary key constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id => OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exists
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>> Use CREATE INDEX with the DROP EXISTING clause. Example:
>> USE master
>> GO
>> DROP DATABASE FgTest
>> GO
>> CREATE DATABASE FgTest
>> ON PRIMARY
>> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH
>> =>> 1024KB ),
>> FILEGROUP [ForData]
>> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
>> FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB ,
>> FILEGROWTH = 10%)
>> GO
>> USE FgTest
>> CREATE TABLE t(c1 int identity, c2 char(5))
>> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
>> INSERT INTO t (c2)
>> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
>> GO
>> EXEC sp_helpindex t
>> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
>> EXEC sp_helpindex t
>> USE master
>> DROP DATABASE FgTest
>> GO
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>> > If a new filegroup is created on a new disk drive, is there a way to migrate
>> > a specific clustered index to the new filegroup?
>> > --
>> > Regards,
>> > Jamie
>>

Move indexes to new filegroup

If a new filegroup is created on a new disk drive, is there a way to migrate
a specific clustered index to the new filegroup?
--
Regards,
JamieUse CREATE INDEX with the DROP EXISTING clause. Example:
USE master
GO
DROP DATABASE FgTest
GO
CREATE DATABASE FgTest
ON PRIMARY
( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH =
1024KB ),
FILEGROUP [ForData]
( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MAXS
IZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB , MA
XSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
USE FgTest
CREATE TABLE t(c1 int identity, c2 char(5))
CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
INSERT INTO t (c2)
SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
GO
EXEC sp_helpindex t
CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
EXEC sp_helpindex t
USE master
DROP DATABASE FgTest
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
> If a new filegroup is created on a new disk drive, is there a way to migra
te
> a specific clustered index to the new filegroup?
> --
> Regards,
> Jamie|||I'm having trouble with the syntax. Index is created and dropped with
ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
[PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
(
[SupplierXrefKey] ASC
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplierIt
emXrefs')
ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSupp
lierItemXrefs]
WHEN I RUN:
CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
I get an error:
Server: Msg 1907, Level 16, State 1, Line 1
Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
does not match the constraint being enforced by the existing index.
DDRIVE is the additional filegroup I created for this table and which exists
on a drive other than the primary filegroup drive.
--
Regards,
Jamie
"Tibor Karaszi" wrote:

> Use CREATE INDEX with the DROP EXISTING clause. Example:
> USE master
> GO
> DROP DATABASE FgTest
> GO
> CREATE DATABASE FgTest
> ON PRIMARY
> ( NAME = N'FgTest', FILENAME = N'C:\FgTest.mdf' , SIZE = 3072KB , MAXSIZE
= UNLIMITED, FILEGROWTH =
> 1024KB ),
> FILEGROUP [ForData]
> ( NAME = N'ForMyData', FILENAME = N'C:\ForMyData.ndf' , SIZE = 3072KB , MA
XSIZE = UNLIMITED,
> FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FgTest_log', FILENAME = N'C:\FgTest_log.ldf' , SIZE = 2048KB ,
MAXSIZE = 2048GB ,
> FILEGROWTH = 10%)
> GO
> USE FgTest
> CREATE TABLE t(c1 int identity, c2 char(5))
> CREATE CLUSTERED INDEX x ON t(c1) ON "PRIMARY"
> INSERT INTO t (c2)
> SELECT TOP 10000 'Hello' FROM sysobjects, syscolumns
> GO
> EXEC sp_helpindex t
> CREATE CLUSTERED INDEX x ON t(c1) WITH DROP_EXISTING ON "ForData"
> EXEC sp_helpindex t
> USE master
> DROP DATABASE FgTest
> GO
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:563BAAF9-92DA-48C9-906C-2467E1E73C53@.microsoft.com...
>|||Try adding UNIQUE to the index definition, since it comes from a primary key
constraint:
CREATE UNIQUE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
(SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:6FF49395-56FF-453F-8E67-34AEFD91408D@.microsoft.com...[vbcol=seagreen]
> I'm having trouble with the syntax. Index is created and dropped with
> ALTER TABLE [dbo].[tSupplierItemXrefs] ADD CONSTRAINT
> [PK_tSupplierItemXrefs] PRIMARY KEY CLUSTERED
> (
> [SupplierXrefKey] ASC
> ) ON [PRIMARY]
>
> IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
> OBJECT_ID(N'[dbo].[tSupplierItemXrefs]') AND name = N'PK_tSupplier
ItemXrefs')
> ALTER TABLE [dbo].[tSupplierItemXrefs] DROP CONSTRAINT [PK_tSu
pplierItemXrefs]
> WHEN I RUN:
> CREATE CLUSTERED INDEX PK_tSupplierItemXRefs ON tSupplierItemXrefs
> (SupplierXrefKey) WITH DROP_EXISTING ON "DDRIVE"
> I get an error:
> Server: Msg 1907, Level 16, State 1, Line 1
> Cannot re-create index 'PK_tSupplierItemXRefs'. The new index definition
> does not match the constraint being enforced by the existing index.
> DDRIVE is the additional filegroup I created for this table and which exis
ts
> on a drive other than the primary filegroup drive.
> --
> Regards,
> Jamie
>
> "Tibor Karaszi" wrote:
>

Move Filegroup another disk

I can move filegroup of tempdb another disk
Actually is c:\. I need move to e
ThankYou use the ALTER DATABASE command. See Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:DDDE61FC-2A05-4548-92CD-086E3C1705E2@.microsoft.com...
> I can move filegroup of tempdb another disk?
> Actually is c:\. I need move to e:
> Thanks
>

Move existing tables to a new Filegroup!

Hi All,
Is there any tools that I can use OR is there any way
that I can move existing tables in PRIMARY group to a new
filegroup programmingly. I have about 500 tables with
data on them that need to be moved to a new filegroup.
Thanks,
tom
If the tables have a clustered index (and most should) you can drop the CI
and recreate it specifying the other Filegroup and it will move the data
over. If you want to move the non-clustered indexes you will have to drop
and recreate them as well. This can all be done programmatically. But be
prepared for it to take a while if there is a lot of data and make sure your
log file can handle the load.
Andrew J. Kelly SQL MVP
"tt" <anonymous@.discussions.microsoft.com> wrote in message
news:480001c47363$df1294f0$a301280a@.phx.gbl...
> Hi All,
> Is there any tools that I can use OR is there any way
> that I can move existing tables in PRIMARY group to a new
> filegroup programmingly. I have about 500 tables with
> data on them that need to be moved to a new filegroup.
> Thanks,
> tom

Move existing table to another file or filegroup

Hi,
is it possible to move existing table to another file or filegroup?
Thanks AlesRe-create the clustered index on that filegroup. If you don't have a cluster
ed index and don't want
to have one, create one on the file group and then drop it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ales vojacek" <ales vojacek@.discussions.microsoft.com> wrote in message
news:F6788339-43BB-480E-A164-16E313CD32C6@.microsoft.com...
> Hi,
> is it possible to move existing table to another file or filegroup?
> Thanks Ales|||It moves data from whole table to new file group?
Ales
"Tibor Karaszi" wrote:

> Re-create the clustered index on that filegroup. If you don't have a clust
ered index and don't want
> to have one, create one on the file group and then drop it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ales vojacek" <ales vojacek@.discussions.microsoft.com> wrote in message
> news:F6788339-43BB-480E-A164-16E313CD32C6@.microsoft.com...
>
>|||Yes. A clustered index is the same as the table (or, more formally: the leaf
pages of the clustered
index are the data pages).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ales vojacek" <alesvojacek@.discussions.microsoft.com> wrote in message
news:295FDF4A-623F-42A6-AA3F-0C0623894240@.microsoft.com...[vbcol=seagreen]
> It moves data from whole table to new file group?
> Ales
> "Tibor Karaszi" wrote:
>|||I wrote a script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com