April 25, 2008 at 11:27 pm
How can we move a table from primary filegroup to a secondary filegroup? Please, let me know.
April 26, 2008 at 12:23 am
There are quite a few ways doing this..Here is the one..
Drop Clustered index on table in primary filegroup and recreate it in secondary filegroup...n you are good.
NJ
April 26, 2008 at 6:36 pm
interesting answer...
can u please tell me how this works...
if we drop the clustered index on primary and recreate it on secondary how will
this move a table from primary to secondary...
can not we use the alter table syntax to acheive this...
Thanks in advance for sparing your time.
April 26, 2008 at 10:29 pm
Leaf level of the clustered index contains the actual data rows of the table , so moving clustered index means moving data.
Test yourself..
alter database pubs add filegroup fg_test
go
alter database pubs
add file
( name = test2,
filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\test_2.ndf',
size = 1mb,
maxsize = 10mb,
filegrowth = 1mb)
to filegroup fg_test
--create test table
create table test
(
id int identity(1,1),
objname varchar(100) NOT NULL
) on [primary]
go
--create clustered index
CREATE CLUSTERED INDEX ix_test ON dbo.test(id)
ON [PRIMARY]
GO
--insert some data
insert into test(objname)
select name
from sys.objects where type = 'U'
--cHECK FILEGROUP
sp_help Test
--To move the table recreate clustered index on other filegroup
CREATE CLUSTERED INDEX ix_test ON dbo.test(id)
WITH (DROP_EXISTING = ON)
ON fg_test
GO
--Recheck Filegroup
sp_help Test
Alter table with 'MOVE TO' can also be used to move table as i already said in my earlier post that
there are number of ways doing this..
Also MOVE TO will not work if you have nonclustered index...
Hope this helps..
NJ
April 27, 2008 at 11:01 am
thanks a lot....
u were really nice and help full
April 28, 2008 at 2:20 am
Hi
you can do the same from SSMS. Check out "Moving tables" in BOL.
"Keep Trying"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply