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
