Início > Banco de Dados, SQL Server > Particionamento de Tabela com tabelas já populadas

Particionamento de Tabela com tabelas já populadas

Ola galera, esta semana precisei particionar uma tabela que já possuia dados inseridos e encontrei uma certa dificuldade para encontrar material (ou procurei de forma errada… hehehe).

Segue abaixo um passo a passo explicando como podemos fazer neste tipo de caso. Aproveito para agradeçer ao Marcelo Fernandes (@marcelodba) pelo auxilio no Forum do TechNet, Valeu!

— Criação do Ambiente
CREATE DATABASE DBTeste2
USE DBTeste2

CREATE TABLE TbCpd (
CdCpd int identity(1,1)
,    DtCpdCon SmallDateTime NOT NULL
,    DtCpdEmi SmallDateTime
,    CdTop int
,    CdFrn int
)
GO
CREATE TABLE TbCpo (
CdCpo int identity(1,1) primary key
,    CdObj int
,    CdFin int
,    CdCpd int
)
GO
ALTER TABLE TbCpd Add Constraint pk_TbCpd Primary key (CdCpd)
GO
ALTER TABLE TbCpo Add Constraint fk_TbCpo_Cpd Foreign key (CdCpd) references  TbCpd (CdCpd)
GO
INSERT INTO TbCpd values (‘20060512’, ‘20060512’, 123, 456)
INSERT INTO TbCpd values (‘20070613’, ‘20070613’, 123, 456)
INSERT INTO TbCpd values (‘20080717’, ‘20080717’, 123, 456)
INSERT INTO TbCpd values (‘20060330’, ‘20060330’, 123, 456)
INSERT INTO TbCpd values (‘20090215’, ‘20090215’, 123, 456)
INSERT INTO TbCpd values (‘20100101’, ‘20100101’, 123, 456)
INSERT INTO TbCpd values (‘20110920’, ‘20110920’, 123, 456)
INSERT INTO TbCpd values (‘20060612’, ‘20060612’, 123, 456)
INSERT INTO TbCpd values (‘20070713’, ‘20070713’, 123, 456)
INSERT INTO TbCpd values (‘20080817’, ‘20080817’, 123, 456)
INSERT INTO TbCpd values (‘20060430’, ‘20060430’, 123, 456)
INSERT INTO TbCpd values (‘20090315’, ‘20090315’, 123, 456)
INSERT INTO TbCpd values (‘20100201’, ‘20100201’, 123, 456)
INSERT INTO TbCpd values (‘20111020’, ‘20111020’, 123, 456)
GO
INSERT INTO TbCpo values (76, 13, 1)
INSERT INTO TbCpo values (76, 153, 3)
INSERT INTO TbCpo values (76, 13, 2)
INSERT INTO TbCpo values (76, 133, 5)
INSERT INTO TbCpo values (76, 13, 4)
INSERT INTO TbCpo values (76, 13, 4)
INSERT INTO TbCpo values (76, 123, 5)
INSERT INTO TbCpo values (76, 13, 6)
INSERT INTO TbCpo values (76, 13, 7)
INSERT INTO TbCpo values (76, 113, 7)
INSERT INTO TbCpo values (76, 13, 7)
INSERT INTO TbCpo values (76, 138, 7)
INSERT INTO TbCpo values (76, 13, 7)
INSERT INTO TbCpo values (76, 13, 9)
INSERT INTO TbCpo values (76, 136, 10)
INSERT INTO TbCpo values (76, 13, 11)
INSERT INTO TbCpo values (76, 134, 12)
INSERT INTO TbCpo values (76, 13, 13)
INSERT INTO TbCpo values (76, 13, 14)
INSERT INTO TbCpo values (76, 132, 14)
INSERT INTO TbCpo values (76, 13, 14)
INSERT INTO TbCpo values (76, 131, 1)
INSERT INTO TbCpo values (76, 13, 5)
INSERT INTO TbCpo values (76, 13, 5)
INSERT INTO TbCpo values (76, 13, 2)

USE DBTeste2
Select * From TbCpd
Select * From TbCpo

/******************
 **** OBJETIVO ****
 ******************/
— O Objetivo é particionar a Tabela TbCpd em valores inferiores e posteriores a 12, onde iremos:
— – Armazenar os códigos de 1 a 12 no FILEGROUP: FGA12
— – Armazenar os códigos de 12 em diante no FILEGROUP: FGD12

/*************************
 * Criando os FileGroups *
 *************************/
ALTER DATABASE DBTeste2 ADD FILEGROUP [FGA12] — Até 12
ALTER DATABASE DBTeste2 ADD FILEGROUP [FGD12] — Depois de 12

— Criando arquivos secundários e associando aos FileGroups
— Dados com código menor ou igual a 12
ALTER DATABASE DBTeste2
ADD FILE ( NAME = N’DadosA12′, FILENAME = N’S:\Dados\DadosA12.ndf’ , SIZE = 128MB , FILEGROWTH = 64MB )
TO FILEGROUP [FGA12]

— Dados com código maior que 12
ALTER DATABASE DBTeste2
ADD FILE ( NAME = N’DadosD12′, FILENAME = N’S:\Dados\DadosD12.ndf’ , SIZE = 128MB , FILEGROWTH = 64MB )
TO FILEGROUP [FGD12]

/*******************
 * PARTICIONAMENTO *
 *******************/
— Função de Partição
CREATE PARTITION FUNCTION Func_Part (int) — Tipo do Campo, no nosso caso INT
AS RANGE LEFT FOR VALUES (12); — O 12 seria o “ponto de divisão”

— RANGE LEFT
— <= 12
— > 12

— RANGE RIGHT
— < 12
— >= 12

— Esquema de Partição
CREATE PARTITION SCHEME Sch_Part AS PARTITION Func_Part TO (FGA12, FGD12);

— Consultando as partições criadas
SELECT * FROM SYS.partition_functions
SELECT * FROM SYS.partition_range_values
SELECT * FROM SYS.partition_schemes

— Testando em que partição será armazenado o valor, no teste informei o 15 (Que irá ficar na Partição 2 [FGD12])
SELECT $Partition.Func_Part(15) AS Particao

— ***** O SECREDO quando já existem dados inseridos e a tabela que será particionada esta relacionada
— a outra(s) tabela(s) (FOREIGN KEY)

—    Exclui a Foreign KEY associada a Tabela que deseja Particionar, no nosso caso a TbCpo esta relacionada com a
— TbCpd (Que será particionada)
ALTER TABLE TbCpo DROP CONSTRAINT fk_TbCpo_Cpd;
GO
— Exclui a PRIMARY KEY da Tabela que será Particionada e Move para o Esquema de Partição (Sch_Part)
ALTER TABLE TbCpd DROP CONSTRAINT pk_TbCpd WITH (MOVE TO Sch_Part(CdCpd));
GO
— RECRIAR a PRIMARY KEY da Tabela agora Particionada e aponta-o para o Esquema de Partição (Sch_Part)
ALTER TABLE TbCpd ADD CONSTRAINT  pk_TbCpd PRIMARY KEY(CdCpd) on Sch_Part(CdCpd);
GO
— RECRIAR a FOREIGN KEY da Tabela TbCpo com a TbCpd (Tabela Particionada)
ALTER TABLE TbCpo ADD CONSTRAINT  fk_TbCpo_Cpd FOREIGN KEY (CdCpd) references  TbCpd (CdCpd)
go

/*******************************************************
 ***** PRONTO A TABELA JÁ SE ENCONTRA PARTICIONADA *****
 *******************************************************/

— Mostra quantidade de registros por partição
SELECT    OBJECT_NAME(p.object_id) as obj_name
,        p.index_id
,        p.partition_number
,        d.name
,        p.rows
,        a.type
,        a.filegroup_id
FROM        sys.system_internals_allocation_units a
JOIN        sys.partitions p    ON p.partition_id = a.container_id
left join    sys.data_spaces d    ON d.data_space_id= a.filegroup_id
WHERE    p.object_id = (OBJECT_ID(N’TbCpd’))
and        p.index_id = 1
ORDER BY
obj_name
,        p.index_id
,        p.partition_number

— Inserindo mais alguns dados
INSERT INTO TbCpd values (‘20110921’, ‘20110920’, 25, 789)
INSERT INTO TbCpd values (‘20110921’, ‘20110920’, 25, 789)

INSERT INTO TbCpo values (76, 13, 15)
INSERT INTO TbCpo values (76, 153, 15)
INSERT INTO TbCpo values (76, 13, 16)

— Se quiser de uma olhada novamente na consulta (Mostra quantidade de registros por Partição) e observe o resultado.

— Desfazendo o cenário
— DROP TABLE TbCpo
— GO
— DROP TABLE TbCpd
— GO
— DROP PARTITION SCHEME Sch_Part
— GO
— DROP PARTITION FUNCTION Func_Part


Vejam também:

Particionamento de Tabelas com o SQL Server 2005

Múltiplos arquivos por filegroup

  1. Nenhum comentário ainda.
  1. 28/10/2013 às 09:59

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: