placing existing table on a different filegroup

Hi ,
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:

> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com