File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?
sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
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
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> 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
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group
> to seperate User File Groups?
> Enterprise Manager?
>