Post reply

Movinga table

  • April 25, 2008 at 11:27 pm

    #185785

    How can we move a table from primary filegroup to a secondary filegroup? Please, let me know.

  • April 26, 2008 at 12:23 am

    #807490

    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

    #807528

    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

    #807534

    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

    #807561

    thanks a lot....

    u were really nice and help full

  • April 28, 2008 at 2:20 am

    #807636

    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