Gerencie a retenção de dados históricos em tabelas temporais versionadas pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Base de Dados SQL do Azure Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Uma tabela temporal com versão controlada pelo sistema mantém todas as versões anteriores de cada linha na respetiva tabela de histórico. A tabela de histórico pode aumentar o tamanho da sua base de dados mais do que as tabelas regulares sob as seguintes condições:

• Retém dados históricos durante um longo período de tempo.
• Tem um padrão de modificação de dados com muitas atualizações ou eliminações.

Uma tabela de histórico grande e em constante crescimento pode tornar-se um problema, tanto devido aos custos de armazenamento como ao imposto de desempenho que impõe às consultas temporais. Desenvolver uma política de retenção de dados para a tabela de histórico é uma parte importante do planeamento e gestão do ciclo de vida de cada tabela temporal.

Planeie uma política de retenção de dados

Para gerir a retenção de dados de tabelas temporais, determine-se primeiro o período de retenção necessário para cada tabela temporal. A sua política de retenção, na maioria dos casos, deve fazer parte da lógica de negócio da aplicação que utiliza as tabelas temporais. Por exemplo, aplicações em auditoria de dados e cenários de viagem no tempo têm requisitos firmes sobre quanto tempo os dados históricos devem estar disponíveis para consultas online.

Depois de determinar o seu período de retenção de dados, desenvolva um plano para gerir os dados históricos. Decida como e onde você armazena seus dados históricos e como excluir dados históricos mais antigos do que seus requisitos de retenção.

Cada abordagem neste artigo atua sobre a coluna que corresponde ao fim do período na tabela atual, que é a ValidTo coluna nos exemplos que se seguem. O valor de fim de período para cada linha determina o momento em que a versão da linha se torna fechada, ou seja, quando ela pousa na tabela de histórico. Por exemplo, a condição ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) corresponde a dados históricos com mais de 30 dias.

Escolha uma das seguintes abordagens para executar uma ação sobre essas linhas:

Os exemplos de particionamento e de limpeza personalizada deste artigo baseiam-se nas amostras do artigo Criar uma tabela temporal com versão do sistema.

Use uma política de retenção de histórico temporal

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Base de Dados SQL do Azure, Azure SQL Managed Instance e base de dados SQL no Microsoft Fabric.

Pode configurar a retenção de histórico temporal ao nível da tabela individual, o que lhe permite criar políticas flexíveis de envelhecimento. Para permitir a retenção temporal, defina HISTORY_RETENTION_PERIOD durante a criação da tabela ou durante uma alteração do esquema.

Depois de definir a política de retenção, o Database Engine executa uma tarefa agendada em segundo plano que encontra e remove de forma transparente linhas históricas cujo valor no final do período é mais antigo do que o período de retenção.

Como configurar a política de retenção

Antes de configurar a política de retenção para uma tabela temporal, verifique se a retenção histórica temporal está habilitada no nível do banco de dados:

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

A opção da base de dados is_temporal_history_retention_enabled tem como predefinição ON, mas pode alterá-la utilizando a instrução ALTER DATABASE. O Database Engine também o define para OFF automaticamente após uma operação de restauro para um ponto no tempo (PITR), conforme descrito em Considerações sobre o restauro para um ponto no tempo. Para ativar a limpeza da retenção do histórico temporal no seu banco de dados, execute a instrução a seguir. Substitua <myDB> pela base de dados que pretende alterar:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Pode configurar a política de retenção durante a criação da tabela especificando um valor para o HISTORY_RETENTION_PERIOD parâmetro:

CREATE TABLE dbo.WebsiteUserInfo
(
    UserID INT NOT NULL PRIMARY KEY CLUSTERED,
    UserName NVARCHAR (100) NOT NULL,
    PagesVisited INT NOT NULL,
    ValidFrom DATETIME2 (0) GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

Com essa política em vigor, as linhas em dbo.WebsiteUserInfoHistory tornam-se elegíveis para limpeza quando cumprem a seguinte condição:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Pode especificar o período de retenção em DAYS, WEEKS, MONTHS, ou YEARS. Se omitir HISTORY_RETENTION_PERIOD, a retenção fica, por defeito, definida como INFINITE. Você também pode usar a palavra-chave INFINITE explicitamente.

Em alguns cenários, pode querer configurar a retenção após a criação da tabela ou alterar o valor previamente configurado. Nesse caso, use a instrução ALTER TABLE:

ALTER TABLE dbo.WebsiteUserInfo
    SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Para revisar o estado atual da política de retenção, use o exemplo a seguir. Esta consulta associa o indicador de ativação de retenção temporal ao nível do banco de dados com os períodos de retenção para tabelas individuais.

SELECT DB.is_temporal_history_retention_enabled,
       SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
       T1.name AS TemporalTableName,
       SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
       T2.name AS HistoryTableName,
       T1.history_retention_period,
       T1.history_retention_period_unit_desc
FROM sys.tables AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Como o Mecanismo de Banco de Dados exclui linhas antigas

O processo de limpeza depende do layout de índice da tabela de histórico. Só é possível configurar uma política de retenção finita em tabelas de histórico que tenham um índice rowstore agrupado (árvore B) ou um índice columnstore agrupado. Uma tarefa em segundo plano realiza a limpeza de dados envelhecidos para todas as tabelas temporais com um período de retenção finito.

Índice de rowstore da árvore B

O índice agrupado rowstore deve começar com a coluna correspondente ao fim do período SYSTEM_TIME. Se tal índice não existir, não pode configurar um período finito de retenção:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

A tabela de histórico predefinida já tem um índice agrupado em conformidade. Se tentar colocar esse índice numa tabela de histórico com um período de retenção finito, a operação falha com o seguinte erro:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

A lógica de limpeza do índice clusterizado rowstore elimina linhas envelhecidas em blocos mais pequenos (até 10.000), minimizando a pressão sobre o registo da base de dados e o subsistema de I/O. Embora a lógica de limpeza utilize o índice B-tree necessário, não consegue garantir a ordem de eliminação dos registos anteriores ao período de retenção. Não dependa da ordem de limpeza em seus aplicativos.

Índice columnstore agrupado

A tarefa de limpeza para o armazenamento de colunas em clusters remove grupos inteiros de linhas de uma só vez. Cada grupo de linhas contém tipicamente um milhão de linhas. Este método é mais eficiente, especialmente quando a sua carga de trabalho gera dados históricos a um ritmo elevado.

A compressão de dados e a limpeza de retenção tornam o índice clustered columnstore uma boa escolha para cenários em que a sua carga de trabalho gera rapidamente uma grande quantidade de dados históricos. Esse padrão é típico de cargas de trabalho de processamento transacional intensivo que utilizam tabelas temporais para acompanhamento e auditoria de alterações, análise de tendências ou ingestão de dados da Internet das Coisas (IoT).

A limpeza no índice clustered columnstore funciona de forma ótima quando as linhas históricas chegam em ordem crescente (ordenadas pela coluna de fim de período). Esta condição é sempre o caso quando apenas o SYSTEM_VERSIONING mecanismo preenche a tabela de histórico. Se as linhas na tabela de histórico não estiverem ordenadas pela coluna de fim do período (o que pode acontecer ao migrar dados históricos existentes), recrie o índice columnstore agrupado sobre um índice rowstore de árvore B devidamente ordenado para obter o melhor desempenho.

Evite reconstruir o índice clustered columnstore numa tabela de histórico com um período de retenção finito, pois reconstruir pode alterar a ordem dos grupos de linhas que a operação de versionamento do sistema impõe naturalmente. Se precisar de reconstruir o índice columnstore em cluster na tabela de histórico, recrie-o com base num índice B-tree compatível para preservar a ordenação dos grupos de linhas necessária para a limpeza regular dos dados. Siga a mesma abordagem se criar uma tabela temporal com uma tabela de histórico existente que tenha um índice de coluna agrupado sem ordem garantida dos dados:

/* Create B-tree ordered by the end-of-period column */
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory(ValidTo) WITH (DROP_EXISTING = ON);
GO

/* Re-create the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Quando configuras um período de retenção finito para uma tabela de histórico com um índice de coluna clusterizado, não podes criar índices B não agrupados adicionais nessa tabela:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

A instrução anterior falha com o seguinte erro:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Tabelas de consulta com política de retenção

Todas as consultas na tabela temporal filtram automaticamente as linhas históricas que correspondem à política de retenção finita, para evitar resultados imprevisíveis e inconsistentes. A tarefa de limpeza elimina linhas antigas em qualquer momento e por ordem arbitrária.

A captura de ecrã seguinte mostra o plano de consulta para uma consulta básica. Este exemplo assume um período de retenção de um anoMONTH na tabela WebsiteUserInfo:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

O plano de consulta inclui um filtro extra na coluna de fim de período (ValidTo) no operador Clustered Index Scan (destacado na imagem seguinte) na tabela de histórico.

Se consultar diretamente a tabela de histórico, pode ver linhas mais antigas do que o período de retenção especificado, mas sem qualquer garantia de resultados de consulta repetíveis. A captura de ecrã seguinte mostra o plano de consulta para uma consulta na tabela de histórico sem filtros adicionais:

Não confie na lógica de negócio que lê a tabela de histórico para além do período de retenção, porque pode obter resultados inconsistentes ou inesperados. Use consultas temporais com a FOR SYSTEM_TIME cláusula para analisar dados em tabelas temporais.

Considerações de restauração num ponto no tempo

Quando restauras uma base de dados para um ponto específico no tempo, a nova base de dados tem a retenção temporal desativada ao nível da base de dados (is_temporal_history_retention_enabled definido para OFF). Este comportamento permite-lhe inspecionar linhas históricas mais antigas do que o período de retenção antes de a tarefa de limpeza as remover. Para retomar a limpeza automática da base de dados restaurada, defina TEMPORAL_HISTORY_RETENTION novamente para ON.

Usar particionamento de tabelas

Tabelas particionadas e índices podem tornar tabelas grandes mais gerenciáveis e escaláveis. Ao usar a abordagem de particionamento de tabelas, pode implementar limpeza personalizada de dados ou arquivamento offline com base numa condição temporal. O particionamento de tabelas também oferece benefícios de desempenho ao consultar tabelas temporais em um subconjunto do histórico de dados, usando a eliminação de partições.

Utilize o particionamento da tabela para implementar uma janela deslizante que permita remover da tabela de histórico a parte mais antiga dos dados históricos e manter constante, em função da antiguidade, o tamanho da parte retida. Uma janela deslizante mantém os dados na tabela de histórico iguais ao período de retenção exigido. A tabela de histórico suporta a substituição de dados enquanto SYSTEM_VERSIONING está ON, o que significa que pode eliminar uma parte dos dados do histórico sem introduzir uma janela de manutenção nem bloquear as suas cargas de trabalho normais.

Uma janela deslizante requer dois conjuntos de tarefas:

• Uma tarefa de configuração de particionamento
• Tarefas recorrentes de manutenção de partições

Para esta ilustração, suponha que quer manter dados históricos durante seis meses e que quer manter cada mês de dados numa partição separada. Além disso, suponha que ativou o controlo de versões do sistema em setembro de 2023.

Uma tarefa de configuração de particionamento cria a configuração de particionamento inicial para a tabela de histórico. Neste exemplo, cria-se o mesmo número de partições que o tamanho da janela deslizante, em meses, mais uma partição vazia extra. Esta configuração garante que o sistema pode armazenar os novos dados corretamente quando inicia a tarefa recorrente de manutenção da partição. Também garante que nunca se dividem partições que contêm dados, o que evita movimentos dispendiosos de dados. Defina a função de partição com RANGE LEFT em vez de RANGE RIGHT. Para mais informações, consulte Considerações de desempenho com particionamento de tabelas mais adiante neste artigo.

A imagem seguinte mostra a configuração inicial de partição para manter seis meses de dados.

A primeira e a última partição estão abertas nos limites inferior e superior, respetivamente, para garantir que cada nova linha tenha uma partição de destino, independentemente do valor na coluna de partição. Com o tempo, novas linhas na tabela de história aparecem em partições superiores. Quando a sexta partição se preenche, atinge-se o período de retenção previsto. Neste ponto, inicia a tarefa recorrente de manutenção da partição pela primeira vez. Agende-o para correr periodicamente, uma vez por mês neste exemplo.

A imagem seguinte ilustra as tarefas recorrentes de manutenção das partições.

Cada execução da tarefa de manutenção recorrente executa os seguintes passos:

1. SWITCH OUT: Criar uma tabela de preparação e, em seguida, comutar uma partição entre a tabela de histórico e a tabela de preparação utilizando a instrução ALTER TABLE com o argumento SWITCH PARTITION.

   ALTER TABLE [<history table>]
       SWITCH PARTITION 1 TO [<staging table>];
   

   Após a mudança de partição, pode, opcionalmente, arquivar os dados da tabela de preparação e, em seguida, eliminar ou truncar a tabela de preparação para preparar o próximo ciclo de manutenção.

2. MERGE RANGE: Intercale a partição vazia 1 com a partição 2 utilizando a instrução ALTER PARTITION FUNCTION com MERGE RANGE. Quando usa esta função para remover a fronteira mais baixa, efetivamente funde a partição 1 vazia com a partição 2 anterior para formar uma nova partição 1. As outras partições também alteram efetivamente os seus ordinais.

3. SPLIT RANGE: Criar uma nova partição 7 vazia usando a ALTER PARTITION FUNCTION instrução com SPLIT RANGE. Quando usa esta função para adicionar um novo limite superior, cria efetivamente uma partição separada para o mês seguinte.

Use Transact-SQL para criar partições na tabela de histórico

Utilize o seguinte script Transact-SQL para criar a função de partição, o esquema de partição e recriar o índice clusterizado para ficar alinhado com o esquema de partição. Neste exemplo, você cria uma janela deslizante de seis meses com partições mensais, a partir de setembro de 2023.

BEGIN TRANSACTION;

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo](DATETIME2 (7))
    AS RANGE LEFT FOR VALUES (
        N'2023-09-30T23:59:59.999',
        N'2023-10-31T23:59:59.999',
        N'2023-11-30T23:59:59.999',
        N'2023-12-31T23:59:59.999',
        N'2024-01-31T23:59:59.999',
        N'2024-02-29T23:59:59.999'
    );

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
    TO (
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY],
        [PRIMARY]
    );

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
)
ON [sch_Partition_DepartmentHistory_By_ValidTo] (ValidTo);

COMMIT TRANSACTION;

Use Transact-SQL para manter partições no cenário de janela deslizante

Use o seguinte script Transact-SQL para manter partições no cenário de janela deslizante. Neste exemplo, troca a partição de setembro de 2023 usando MERGE RANGE, e depois adiciona uma nova partição para março de 2024 usando SPLIT RANGE.

BEGIN TRANSACTION;

/* (1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023]
(
    DeptID INT NOT NULL,
    DeptName VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 (7) NOT NULL,
    ValidTo DATETIME2 (7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/* (2) Create index on the same filegroups as the partition to switch out */
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023](
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY];

/* (3) Create constraints matching the partition to switch out */
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023] WITH CHECK
    ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
            CHECK (ValidTo <= N'2023-09-30T23:59:59.999');

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1];

/* (4) Switch partition to staging table */
ALTER TABLE [dbo].[DepartmentHistory]
    SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE
        )
    );

/* (5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/* (6) merge range to move lower boundary one month ahead */
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE (N'2023-09-30T23:59:59.999');

/* (7) Create new empty partition for "April and after"
by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
    NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE (N'2024-03-31T23:59:59.999');

COMMIT TRANSACTION;

No entanto, a solução ideal é executar regularmente um script genérico de Transact-SQL todos os meses sem modificações. Podes generalizar o script anterior para agir com base nos parâmetros fornecidos (o limite inferior que precisa de ser fundido e o novo limite criado pela divisão da partição). Para evitar criar uma tabela de staging todos os meses, crie-a antecipadamente e reutilize-a, alterando a restrição CHECK para corresponder à partição que remove. Para obter mais informações, consulte como automatizar totalmente o cenário da janela deslizante.

Considerações sobre o desempenho no particionamento de tabelas

Efetue as operações MERGE RANGE e SPLIT RANGE de forma a evitar a transferência de dados, pois a transferência de dados pode causar uma sobrecarga significativa. Para obter mais informações, consulte Modificar uma função de partição.

Quando crias a função de partição como RANGE LEFT, os valores especificados são os limites superiores das partições. Quando você usa RANGE RIGHT, os valores especificados são os limites inferiores das partições. Quando você usa a operação MERGE RANGE para remover um limite da definição de função de partição, a implementação subjacente também remove a partição que contém o limite. Se essa partição não estiver vazia, MERGE RANGE move os dados para a partição resultante.

O diagrama a seguir descreve as opções de RANGE LEFT e RANGE RIGHT:

Em um cenário de janela deslizante, você sempre remove o limite de partição mais baixo .

• RANGE LEFT caso: O limite mais baixo da partição pertence à partição 1, que está vazia (após a troca de partição), por isso MERGE RANGE não causa qualquer movimento de dados.

• RANGE RIGHT caso: O limite inferior da partição pertence à partição 2, que não está vazia porque a substituição só esvazia a partição 1. Neste caso, MERGE RANGE causa movimentação de dados, movendo dados de partição 2 para partição 1. Para evitar este movimento de dados, RANGE RIGHT no cenário da janela deslizante é necessário ter partição 1, que está sempre vazia. Este requisito significa que, se usar RANGE RIGHT, deve criar e manter uma partição extra em comparação com o caso de RANGE LEFT.

Conclusão: A gestão de partições é mais fácil quando se usa RANGE LEFT numa partição deslizante e evita o movimento de dados. No entanto, definir limites de partição com RANGE RIGHT é um pouco mais fácil, porque você não precisa lidar com problemas de verificação de data e hora.

Usa um script de limpeza personalizado

Quando uma política de retenção não está disponível para a sua tabela e a partição da tabela não é viável, pode apagar os dados da tabela de histórico usando um script de limpeza personalizado. Este processo só é possível quando SYSTEM_VERSIONING = OFF. Para evitar inconsistências nos dados, realiza a limpeza durante uma janela de manutenção (quando cargas de trabalho que modificam dados não estão ativas), ou dentro de uma transação (bloqueando efetivamente outras cargas de trabalho). Esta operação requer permissão CONTROL nas tabelas atuais e de histórico.

A lógica de limpeza é a mesma para todas as tabelas temporais, por isso podes automatizá-la através de um procedimento guardado genérico. Use o SQL Server Agent ou uma ferramenta diferente para agendar esse procedimento para correr todos os dias, iterando sobre cada tabela temporal para a qual pretende limitar o histórico de dados.

O diagrama seguinte ilustra como organizar a sua lógica de limpeza para uma única tabela para reduzir o efeito nas cargas de trabalho em execução.

Aqui estão algumas diretrizes gerais para implementar o processo:

• Apague dados históricos em todas as tabelas temporais em várias iterações de pequenos blocos. Começa pelas linhas mais antigas e avança para as mais recentes. Evite eliminar todas as linhas de uma única transação, como mostra o diagrama anterior. Embora nenhum tamanho único de bloco funcione para todos os cenários, eliminar mais de 10.000 linhas numa única transação pode impor uma penalização significativa.

• Implementar cada iteração como uma invocação de um procedimento armazenado genérico, que remove uma parte dos dados da tabela de histórico.

• Calcule quantas linhas você precisa excluir para uma tabela temporal individual sempre que invocar o processo. Com base no resultado e no número de iterações que queres, determina pontos de divisão dinâmicos para cada invocação de procedimento.

• Planeie um atraso entre iterações para uma única tabela, para reduzir o efeito nas aplicações que acedam à tabela temporal.

O procedimento armazenado seguinte elimina os dados de uma única tabela temporal. Descobre a tabela de histórico e a coluna de fim de período a partir das vistas de catálogo, e depois executa três instruções dentro de uma transação: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, e SET SYSTEM_VERSIONING = ON. Revê este código cuidadosamente e ajusta-o antes de o aplicares no teu ambiente.

No SQL Server 2016 (13.x), as duas primeiras etapas devem ser executadas em instruções EXECUTE separadas ou o SQL Server gera um erro semelhante ao exemplo a seguir:

Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO

CREATE PROCEDURE usp_CleanupHistoryData (
    @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS SYSNAME;

/* Generate script to discover history table name and
end of period column for given temporal table name */
EXECUTE sp_executesql N'
    SELECT @hst_tbl_nm = t2.name,
           @hst_sch_nm = s2.name,
           @period_col_nm = c.name
    FROM sys.tables AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS c
             ON p.end_column_id = c.column_id
            AND c.object_id = t1.object_id
    WHERE t1.name = @tblName
          AND s1.name = @schName',
    N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT;

IF @historyTableName IS NULL
   OR @historyTableSchema IS NULL
   OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

SET @disableVersioningScript = @disableVersioningScript +
    'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = OFF)';

SET @deleteHistoryDataScript = @deleteHistoryDataScript +
    ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
    WHERE [' + @periodColumnName + '] < ' + '''' +
    CONVERT (VARCHAR (128), @cleanupOlderThanDate, 126) + '''';

SET @enableVersioningScript = @enableVersioningScript +
    ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' +
    @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); ';

BEGIN TRANSACTION;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Conteúdo relacionado

• Tabelas temporais
• Comece a utilizar tabelas temporais versionadas pelo sistema
• Verificações de consistência do sistema de tabela temporal
• Partição com tabelas temporais
• Considerações e limitações da tabela temporal
• Segurança da tabela temporal
• Tabelas temporais versionadas pelo sistema com tabelas otimizadas para memória
• Exibições e funções de metadados de tabela temporal