Particionamento Dinâmico - Dynamic Partitioning

A Stored Procedure (SQL Server) a seguir realiza o particionamento de uma tabela de nome "FACT_TABLE", o particionamento também tem a função de realizar a manutenção de expurgo da tabela. O código abaixo armazena os últimos 30 dias carregados na tabela "FACT_TABLE", e mantém o histórico dos meses fechados armazenando somente o último dia de cada mês já fechado.
Muito útil para processos que acompanham vendas diárias, mas precisam manter a última posição fechada; posições de Carteiras e etc.

The Stored Procedure (SQL Server) performs the partitioning of a table named "FACT_TABLE" this partitioning also has the function to perform the purge o the table. The code below stores the last 30 days in the loaded table "FACT_TABLE" and keeps track of the months closed storing only the last day of each month closed.
Very useful for processes like daily sales, but need to keep the last closed position; positions and etc.


Código abaixo (Code below)

--Cria função de partição da tabela de fato. (Create the partition function of the fact table)
------------------------------------------------------------------------------------------------------------
DECLARE @DATA SMALLDATETIME

SET @DATA = '1900-01-01'


CREATE PARTITION FUNCTION PFDataRef (SMALLDATETIME)
AS RANGE RIGHT FOR VALUES (@DATA)

GO

-- Cria função de partição da tabela auxiliar. (Creates the partition function of the auxiliary table)
------------------------------------------------------------------------------------------------------------
DECLARE @DATA SMALLDATETIME

SET @DATA = '1900-01-01'

CREATE PARTITION FUNCTION PFDataRefAUX (SMALLDATETIME)
AS RANGE RIGHT FOR VALUES (@DATA)

GO

-- Cria a partição da tabela de fato. (Create the partition of the fact table)
------------------------------------------------------------------------------------------------------------
CREATE PARTITION SCHEME PSDataRef1
AS PARTITION PFDataRef
ALL TO ([PRIMARY])

GO

-- Cria a partição da tabela dauxiliar. (Creates the partition of the auxiliary table)
------------------------------------------------------------------------------------------------------------
CREATE PARTITION SCHEME PSDataRefAUX1
AS PARTITION PFDataRefAUX
ALL TO ([PRIMARY])

GO

-- Cria a tabela de Fato. (Creates the Fact Table)
------------------------------------------------------------------------------------------------------------
CREATE TABLE FACT_TABLE( C1 SMALLDATETIME NOT NULL,
C2 INT NOT NULL) ON PSDataRefCNG1 (C1)

GO

-- Cria a tabela auxiliar. (Creates the Auxiliary Table)
------------------------------------------------------------------------------------------------------------
CREATE TABLE FACT_TABLE_AUX( C1 SMALLDATETIME NOT NULL,
C2 INT NOT NULL) ON PSDataRefCNGAUX1 (C1)

GO

-- Stored Procedure para Particionamento. (Stored Procedure for Partitioning.)
------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE SP_ParticaoExpurgo @DATA SMALLDATETIME
AS

DECLARE @DataPart SMALLDATETIME,
@DataSplit SMALLDATETIME,
@DataRange SMALLDATETIME,
@Cont INT


SET @DataRange =  cast((select top 1 [value] from sys.partition_range_values
where function_id = (select function_id
from sys.partition_functions
where name = 'PFDataRef')
order by boundary_id ASC) as smalldatetime)

IF @DataRange = @DATA
RETURN

ALTER PARTITION SCHEME PSDataRef1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION PFDataRef() SPLIT RANGE (@DATA)

DECLARE Part_cursor CURSOR
FOR SELECT CAST( [value]  AS SMALLDATETIME) AS DATA
FROM sys.partition_range_values
WHERE function_id = (SELECT function_id
FROM sys.partition_functions
WHERE name = 'PFDataRef')
ORDER BY boundary_id DESC

OPEN Part_cursor

FETCH NEXT FROM Part_cursor INTO @DataPart

SET @Cont = 1

CREATE TABLE ##Part (DataPart SMALLDATETIME, Cont INT)

WHILE (@@fetch_status <> -1)
BEGIN

If @Cont <= 30
BEGIN
INSERT INTO ##Part VALUES (@DataPart, @Cont)
END

SET @Cont = @Cont + 1

FETCH NEXT FROM Part_cursor INTO @DataPart
END

CLOSE Part_cursor

DEALLOCATE Part_cursor

DECLARE Split_cursor CURSOR
FOR SELECT CAST( [value] AS SMALLDATETIME) AS DATA
FROM sys.partition_range_values
WHERE function_id = (SELECT function_id
FROM sys.partition_functions
WHERE name = 'PFDataRef')
AND CAST( [value] AS SMALLDATETIME) NOT IN (SELECT DataPart
FROM ##Part
WHERE Cont <= 30
UNION
SELECT MAX(CAST( [value]  AS SMALLDATETIME)) AS DATA
FROM sys.partition_range_values
WHERE function_id = ( select function_id
from sys.partition_functions
where name = 'PFDataRef')
GROUP BY YEAR(CAST( [value]  AS SMALLDATETIME)) * 100 + MONTH(CAST( [value]  AS SMALLDATETIME)) )
ORDER BY boundary_id ASC


OPEN Split_cursor

FETCH NEXT FROM Split_cursor INTO @DataSplit

WHILE (@@fetch_status <> -1)
BEGIN

ALTER PARTITION SCHEME PSDataRefAUX1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION PFDataRefAUX() SPLIT RANGE (@DataSplit)
ALTER TABLE dbo.UFACT_TABLE SWITCH PARTITION $PARTITION.PFDataRef(@DataSplit) TO dbo.FACT_TABLE_AUX PARTITION $PARTITION.PFDataRefAUX(@DataSplit)
TRUNCATE TABLE dbo.FACT_TABLE_AUX
ALTER PARTITION FUNCTION PFDataRefAUX() MERGE RANGE (@DataSplit)
ALTER PARTITION FUNCTION PFDataRef() MERGE RANGE (@DataSplit)

FETCH NEXT FROM Split_cursor INTO @DataSplit
END

CLOSE Split_cursor

DEALLOCATE Split_cursor

DROP TABLE ##Part


-- Teste: (Test)
------------------------------------------------------------------------------------------------------------
exec SP_ParticaoExpurgo '2008-01-01'
INSERT INTO FACT_TABLE VALUES ( '2008-01-01' , 1 )

exec SP_ParticaoExpurgo '2008-01-02'
INSERT INTO FACT_TABLE VALUES ( '2008-01-02' , 2 )

exec SP_ParticaoExpurgo '2008-01-03'
INSERT INTO FACT_TABLE VALUES ( '2008-01-03' , 3 )

/* ... Continue até gerar a quantidade de registros necessários ao teste. Você também pode mudar o valor da procedure de 30 para outro para testar com um volume menor. */
/* ... Continue to generate the quantity of records needed to test. You can also change the value of the quantity of records (less then 30) to test with a smaller volume.*/

-- Consultas para apurar o funcionamento do particionamento. (Queries to test the partitioning operation.)
------------------------------------------------------------------------------------------------------------
SELECT * FROM FACT_TABLE

SELECT *
FROM sys.partition_range_values
WHERE function_id = (SELECT function_id
FROM sys.partition_functions
WHERE name = 'PFDataRef')
ORDER BY boundary_id DESC

SELECT * FROM FACT_TABLE_AUX

SELECT *
FROM sys.partition_range_values
WHERE function_id = (SELECT function_id
FROM sys.partition_functions
WHERE name = 'PFDataRefAux')
ORDER BY boundary_id DESC