Pessoal, tudo se baseia em a gente conseguir criar um indice cluster no novo filegroup. Por que ?
O BOL diz : “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. Então movendo o índice cluster estaremos movendo também a tabela. Pros nossos testes temos o filegroup Primary e estaremos passando pro filegroup NovoFilegroup
Vamos lá..
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 NONCLUSTERED ([Codigo] ASC ) ON [NovoFilegroup]
OU dropar a constraint e recriá-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 NONCLUSTERED ([Codigo] ASC ) ON [NovoFilegroup]
Caso 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, ou seja, voltar a ser uma heap.
CREATE CLUSTERED INDEX [MeuIndiceCluster] ON [Teste_7]([codigo])
ON [NovoFilegroup]
DROP INDEX [Teste_7].MeuIndiceCluster
Caso 3 – Tenho uma tabela com Unique Index NONCLUSTERED e sem Identity. Adiciono um campo identity na tabela e depois crio um indice cluster sobre este campo apontando para o novo filegroup. Logo após 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]
Caso 4 – Tenho uma tabela com Unique Index NONCLUSTERED com Identity. Criamos um indice cluster neste campo identity apontando para o novo filegroup e 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]
Caso 5 – Tenho uma tabela com Unique Index CLUSTERED e sem Identity. Dropamos este unique index ([IX_Tabela_4]) e 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]
Caso 6 – Tenho uma tabela com Unique Index CLUSTERED e com Identity. Dropamos este unique index ([IX_Teste_5]) e 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 precisará ter 1.2 vezes o tamanho original da tabela como espaço livre.
Abraçoslaertejunior