Créditos ao meu amigo Rodrigo Fernandes (Microsoft Brasil), que utiliza estas técnicas com suas pequenas tabelas (11 TB por exmplo). O importante não é ser o cara e sim amigo dele..abraços velhinho !!!! 

Pessoal, uma das perguntas que vejo bastante é como mudar tabelas de filegroups.

Podemos criar uma nova tabela neste filegroup, passar os dados e depois dropar a tabela antiga e renomear a nova no novo filegroup.

Aqui eu mostro como fazer este processo de uma maneira mais simples e ocupando menos recursos do sql server, sendo indicado principalmente para tabelas com grande volume de dados.

Pessoal, tudo se baseia em a gente conseguir criar um indice cluster no novo filegroup. Por que ?

BOL diz :

Note: Because the leaf level of a clustered index and its data pages are the same by definition,
creating a clustered index and using the ON filegroup clause effectively moves a table from the
file on which the table was created to the new filegroup.
Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index.
It is important that the filegroup has at least 1.2 times the space required for the entire table.

Ou seja, por definição o nivel folha de um índice cluster e as suas paginas de dados são os mesmos.
Movendo o índice cluster estaremos movendo a tabela.

Pros nossos testes temos o filegroup Primary e estaremos passando pro filegroup NovoFilegroup

Vamos lá..primeiro caso

1 – Tenho uma tabela com Campo Identity e PK clustered nele.

Podemos fazer assim :

o Alter Table With Move dropa a constraint e recria a tabela no novo filegroup
sendo que depois temos que criar a constraint novamente                               

ALTER TABLE [Teste_1]
DROP CONSTRAINT [PK_Teste_1] with (move to NovoFilegroup)

ALTER TABLE [Teste_1]
WITH NOCHECK ADD CONSTRAINT [PK_Teste_1]
PRIMARY KEY CLUSTERED ([Codigo] ASC  )
ON [NovoFilegroup]
OU                               
Dropar constraint e recria-la novo filegroup
Eu particularmente acho este método mais eficiente pois teremos que recriar a constrainst de qualquer maneira.
ALTER TABLE [Teste_1]
                                DROP CONSTRAINT [PK_Teste_1]
ALTER TABLE [Teste_1]
                                WITH NOCHECK ADD CONSTRAINT [PK_Teste_1]
                                PRIMARY KEY CLUSTERED ([Codigo] ASC  )
                                ON [NovoFilegroup]           

                   
2 – Tenho uma tabela com Campo Identity sem PK ou Indice Cluster, ou seja uma heap.

Criamos um indice cluster no campo identity apontando para o novo filegroup e
depois dropamos este indice para a tabela voltar ao estado original
CREATE CLUSTERED INDEX [MeuIndiceCluster]
ON [Teste_7]([codigo])
ON [NovoFilegroup]

DROP INDEX [Teste_7].MeuIndiceCluster

3 – Tenho uma tabela com Unique Index NONCLUSTERED Sem IDentity

Adiciono um campo identity e depois crio um indice cluster nele apontando para o novo filegroup.
Logo apos dropamos o indice cluster e o campo identity
Dropamos o Unique index ([IX_Teste_2]) e logo após o criamos no novo filegroup
Assim a tabela voltará ao estado original

ALTER TABLE [Teste_2]
ADD [MinhaColunaIdentity] BIGINT IDENTITY (1, 1)

CREATE CLUSTERED INDEX MeuIndiceCluster
ON [Teste_2]([MinhaColunaIdentity])
ON [NovoFilegroup]

DROP INDEX [Teste_2].MeuIndiceCluster

ALTER TABLE [Teste_2]
DROP COLUMN [MinhaColunaIdentity]

DROP INDEX [Teste_2].[IX_Teste_2]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Teste_2]
ON [Teste_2]([Codigo] ASC  )
ON [NovoFilegroup]

4 – Tenho  uma tabela com Unique Index NON CLUSTERED Com IDentity

Criamos um indice cluster neste campo identity apontando para o novo filegroup, dropamos este indice após finalizado.
Dropamos o unique index ([IX_Teste_3]) e o recriamos no novo filegroup

CREATE CLUSTERED INDEX MeuIndiceCluster
ON [Teste_3]([Codigo])
ON [NovoFilegroup]

DROP INDEX [Teste_3].MeuIndiceCluster

DROP INDEX [Teste_3].[IX_Teste_3]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Teste_3]
ON [Teste_3]([Codigo] ASC  )
ON [NovoFilegroup]

5 – Tenho uma tabela com Unique Index CLUSTERED Sem IDentity

Dropamos este unique index([IX_Tabela_4]), recriamos ele apontando para o novo filegroup.

DROP INDEX [Teste_4].[IX_Tabela_4]

CREATE UNIQUE CLUSTERED INDEX [IX_Tabela_4]
ON [Teste_4]([Codigo] ASC  )
ON [NovoFilegroup]

6 – Tenho  uma tabela Unique Index CLUSTERED Com IDentity

Dropamos este unique index([IX_Teste_5]), recriamos ele apontando para o novo filegroup.

DROP INDEX [Teste_5].[IX_Teste_5]
CREATE UNIQUE CLUSTERED INDEX [IX_Teste_5]
ON [Teste_5]([Codigo] ASC  )
ON [NovoFilegroup]

É isso galerinha, sempre lembrando que o novo filegroup precisa ter 1.2 vezes a tabela de espaço livre para isso.

Abraços