Particionamento de Tabelas manual SQL Server.

Pessoal o tema hoje é sobre particionamento de tabelas, a partir da versão 2016 SP1 do SQL Server, essa funcionalidade já é nativa na edição Standard Edition, mas para quem ainda tem ambientes com SQL Server abaixo da 2016, temos uma alternativa que é criar o particionamento manualmente, desta forma segue abaixo exemplo.

1 – Criando o banco e tabela para o particionamento manual:

CREATE DATABASE TESTE;
GO
USE TESTE
GO

SQL

2 – Criando filegroup por estado:

ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_SE;  
ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_PB;  
ALTER DATABASE TESTE  ADD FILEGROUP PARTICAO_AL;  
GO 
-- SE
ALTER DATABASE TESTE   
ADD FILE   
(  
    NAME = PARTICAO_SE,  
    FILENAME = 'E:\temp\PARTICAO_SE.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5%  
)
TO FILEGROUP PARTICAO_SE;
-- PB
ALTER DATABASE TESTE
ADD FILE
(  
    NAME = PARTICAO_PB,  
    FILENAME = 'E:\temp\PARTICAO_PB.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5% 
)
TO FILEGROUP PARTICAO_PB;
-- AL
ALTER DATABASE TESTE
ADD FILE
(  
    NAME = PARTICAO_AL,  
    FILENAME = 'E:\temp\PARTICAO_AL.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 5120MB,  
    FILEGROWTH = 5% 
)
TO FILEGROUP PARTICAO_AL;

SQL

3 – Verificando filegroups criados e seus datafiles por estado:

SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id

SQL

4 – A jogada de mestre aqui é, criação das tabelas segmentadas por estado e usando uma check check estado =’se’,estado =’pb’,estado =’al’:

CREATE TABLE TB_FUNCIONARIO_SE
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='SE'), --CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_SE; -- FILEGROUP SE
 
 
CREATE TABLE TB_FUNCIONARIO_PB
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='PB'),--CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_PB; -- FILEGROUP PB
 
 
CREATE TABLE TB_FUNCIONARIO_AL
 (
MATRICULA INT NOT NULL,
ESTADO CHAR(2) NOT NULL,
CHECK (ESTADO ='AL'),--CHECK
PRIMARY KEY (MATRICULA,ESTADO)
) ON PARTICAO_AL; -- FILEGROUP AL

SQL

5 – Verificando tabelas por filegroup:

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables

SQL

6 – Aqui é a jogada de mestre, criamos uma view apontando para as tabelas segmentadas estado, desta forma as operações vão ser na view e não com acesso direto as tabela.

CREATE VIEW VW_FUNCIONARIOS 
AS
SELECT * FROM TB_FUNCIONARIO_SE
UNION ALL
SELECT * FROM TB_FUNCIONARIO_PB
UNION ALL
SELECT * FROM TB_FUNCIONARIO_AL

SQL

7 – Carga de dados 50 linhas estado al inserindo direto na VIEW VW_FUNCIONARIOS:

DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
    INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'SE');
END

SQL

8 – Carga de dados 50 linhas estado pb inserindo direto na VIEW VW_FUNCIONARIOS:

DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
    INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'PB');
END

SQL

9 – Carga de dados 50 linhas estado al inserindo direto na view VW_FUNCIONARIOS:

DECLARE @i int = 0
WHILE @i < 50 
BEGIN
    SET @i = @i + 1
    INSERT INTO  VW_FUNCIONARIOS (MATRICULA,ESTADO) VALUES (@i,'AL');
END

SQL

10 – Verificando linhas inseridas por tabela do estado:

SELECT * FROM TB_FUNCIONARIO_SE;
SELECT * FROM TB_FUNCIONARIO_PB;
SELECT * FROM TB_FUNCIONARIO_AL;

SQL

11 – SELECT na view que sera usada para todas as operações de select,insert,update,delete:

	
SELECT * FROM VW_FUNCIONARIOS;

SQL

Leave a Reply Cancel reply