Cenários de uso de tabelas temporais

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

As tabelas temporais com versão do sistema são úteis em cenários que exigem o histórico de acompanhamento de alterações de dados. Recomendamos que você considere tabelas temporais nos seguintes casos de uso, para obter grandes benefícios de produtividade.

Auditoria de dados

Você pode usar o controle de versão temporal do sistema em tabelas que armazenam informações críticas, para acompanhar o que mudou e quando, e para executar perícia de dados a qualquer momento.

As tabelas temporais permitem planejar cenários de auditoria de dados nos estágios iniciais do ciclo de desenvolvimento ou adicionar auditoria de dados a aplicativos ou soluções existentes quando necessário.

O diagrama a seguir mostra uma tabela Employee com o exemplo de dados, incluindo versões de linha atuais (marcadas com uma cor azul) e versões de linha históricas (marcadas com uma cor cinza).

A parte direita do diagrama visualiza versões de linha em um eixo de tempo e as linhas selecionadas com diferentes tipos de consulta na tabela temporal, com ou sem a cláusula SYSTEM_TIME.

Habilitar o controle de versão do sistema em uma nova tabela para auditoria de dados

Se identificar informações que precisam de auditoria de dados, crie tabelas de base de dados como tabelas temporais versionadas pelo sistema. O exemplo a seguir ilustra um cenário com uma tabela chamada Employee no banco de dados HR hipotético:

CREATE TABLE Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2(2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2(2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Várias opções para criar uma tabela temporal com versão do sistema são descritas em Criar uma tabela temporal com versão do sistema.

Habilitar o controle de versão do sistema em uma tabela existente para auditoria de dados

Se você precisar executar a auditoria de dados em bancos de dados existentes, use ALTER TABLE para estender tabelas não temporais para se tornarem versionadas pelo sistema. Para evitar alterações disruptivas na sua aplicação, adicione colunas de período como HIDDEN, conforme explicado em Criar uma tabela temporal versionada por sistema.

O exemplo a seguir ilustra a habilitação do versionamento do sistema numa tabela Employee existente em um banco de dados de Recursos Humanos hipotético. Ele permite o controle de versão do sistema na tabela Employee em duas etapas. Primeiro, novas colunas de período são adicionadas na forma de HIDDEN. Em seguida, ele cria a tabela de histórico padrão.

ALTER TABLE Employee ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Importante

A precisão do tipo de dados datetime2 deve ser a mesma na tabela de origem que na tabela de histórico com versão do sistema.

Depois de executar o script anterior, todas as alterações de dados serão coletadas de forma transparente na tabela de histórico. No cenário típico de auditoria de dados, você consultaria todas as alterações de dados que foram aplicadas a uma linha individual dentro de um período de tempo de interesse. A tabela de histórico padrão é criada com uma árvore B de armazenamento de linhas clusterizada, para resolver esse caso de uso de forma eficiente.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de armazenamento em linha, o Mecanismo de Base de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou índices em tabelas com otimização de memória. Para obter mais informações, consulte o SQL Server e o guia de design de arquitetura e design de índice SQL do Azure.

Realizar análise de dados

Depois de habilitar o controle de versão do sistema usando qualquer uma das abordagens anteriores, a auditoria de dados está a apenas uma consulta de distância. A consulta a seguir procura versões de linha para registros na tabela Employee, com EmployeeID = 1000 que estiveram ativas pelo menos por uma parte do período entre 1º de janeiro de 2021 e 1º de janeiro de 2022 (incluindo o limite superior):

SELECT * FROM Employee
FOR SYSTEM_TIME BETWEEN '2021-01-01 00:00:00.0000000'
    AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Substitua FOR SYSTEM_TIME BETWEEN...AND por FOR SYSTEM_TIME ALL para analisar todo o histórico de alterações de dados desse funcionário específico:

SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Para procurar versões de linha que estavam ativas apenas dentro de um período (e não fora dele), use CONTAINED IN. Esta consulta é eficiente porque consulta apenas a tabela de histórico:

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN (
    '2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000'
)
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Finalmente, em alguns cenários de auditoria, você pode querer ver como a tabela inteira parecia em qualquer momento no passado:

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00.0000000';

As tabelas temporais com versão do sistema armazenam valores para colunas de período no fuso horário UTC, mas você pode achar mais conveniente trabalhar em seu fuso horário local, tanto para filtrar dados quanto para exibir resultados. O exemplo de código a seguir mostra como aplicar uma condição de filtragem, que é especificada no fuso horário local e, em seguida, convertida em UTC usando AT TIME ZONE, que foi introduzida no SQL Server 2016 (13.x):

/* Add offset of the local time zone to current time*/
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, - 9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
    [Name],
    Position,
    Department,
    [Address],
    [AnnualSalary],
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
    ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee
FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

Usar o AT TIME ZONE é útil em quaisquer outros cenários em que são usadas tabelas versionadas pelo sistema.

As condições de filtragem especificadas em cláusulas temporais com FOR SYSTEM_TIME são SARG-able. SARG significa argumento de pesquisa, e SARG-able significa que o SQL Server pode usar o índice clusterizado subjacente para executar uma busca em vez de uma operação de verificação. Para obter mais informações, consulte SQL Server Index Architecture and Design Guide.

Se consultar a tabela de histórico diretamente, certifique-se de que a condição de filtragem também é compatível com SARG, especificando filtros na forma de <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Se aplicar AT TIME ZONE às colunas de período, o SQL Server realizará uma varredura de tabela ou índice, o que pode ser muito dispendioso. Evite este tipo de condição nas suas consultas:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Para obter mais informações, consulte Consultar dados em uma tabela temporal com versão do sistema.

Análise pontual no tempo (retrocesso temporal)

Em vez de se concentrar em alterações em registros individuais, os cenários de viagem no tempo mostram como conjuntos de dados inteiros mudam ao longo do tempo. Às vezes, a viagem no tempo inclui várias tabelas temporais relacionadas, cada uma mudando em ritmo independente, para as quais você deseja analisar:

• Tendências para os indicadores importantes nos dados históricos e atuais
• Instantâneo exato de todos os dados "a partir de" qualquer ponto no tempo no passado (ontem, um mês atrás, etc.)
• Diferenças entre dois pontos no tempo de interesse (há um mês vs. há três meses, por exemplo)

Existem muitos cenários do mundo real que requerem análise de viagem no tempo. Para ilustrar esse cenário de uso, vamos examinar o OLTP com histórico gerado automaticamente.

OLTP com histórico de dados gerados automaticamente

Em sistemas de processamento de transações, você pode analisar como as métricas importantes mudam ao longo do tempo. Idealmente, a análise do histórico não deve comprometer o desempenho do aplicativo OLTP, onde o acesso ao estado mais recente dos dados deve ocorrer com latência mínima e bloqueio de dados. Você pode usar tabelas temporais com versão do sistema para manter de forma transparente o histórico completo de alterações para análise posterior, separadamente dos dados atuais, com um impacto mínimo na carga de trabalho OLTP principal.

Para cargas de trabalho de processamento altamente transacional no SQL Server e na Instância Gerida do Azure SQL, recomendamos que utilize tabelas temporais com versionamento do sistema e tabelas otimizadas para memória, que permitem armazenar dados atuais na memória e todo o histórico de alterações no disco de forma económica.

Para a tabela de histórico, recomendamos que você use um índice columnstore clusterizado pelos seguintes motivos:

• A análise típica de tendências beneficia-se do desempenho de consultas fornecido por um índice de armazenamento em colunas clusterizado.

• A tarefa de liberação de dados com tabelas com otimização de memória tem melhor desempenho sob carga de trabalho OLTP pesada quando a tabela de histórico tem um índice columnstore clusterizado.

• Um índice columnstore clusterizado fornece excelente compactação, especialmente em cenários em que nem todas as colunas são alteradas ao mesmo tempo.

O uso de tabelas temporais com OLTP na memória reduz a necessidade de manter todo o conjunto de dados na memória e permite distinguir facilmente entre dados quentes e frios.

Exemplos de cenários do mundo real que se encaixam bem nesta categoria são a gestão de estoque ou a negociação de moedas, entre outros.

O diagrama a seguir mostra o modelo de dados simplificado usado para o gerenciamento de estoque:

O exemplo de código a seguir cria ProductInventory como uma tabela temporal em memória, versionada pelo sistema, com um índice columnstore clusterizado na tabela de histórico (que, na verdade, substitui o índice de armazenamento de linhas criado por padrão):

Observação

Certifique-se de que seu banco de dados permite a criação de tabelas com otimização de memória. Consulte Criando uma tabela Memory-Optimized e um procedimento armazenado compilado nativamente.

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
        SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory] (
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

Para o modelo anterior, é assim que o procedimento para manter o estoque pode parecer:

CREATE PROCEDURE [dbo].[spUpdateInventory]
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    UPDATE dbo.ProductInventory
    SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location

    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

O procedimento armazenado spUpdateInventory insere um novo produto no inventário ou atualiza a quantidade do produto para o local específico. A lógica de negócios é simples e focada em manter o estado mais recente preciso o tempo todo, incrementando/diminuindo o campo Quantity por meio da atualização da tabela, enquanto as tabelas com versão do sistema adicionam de forma transparente a dimensão do histórico aos dados, conforme descrito no diagrama a seguir.

Agora, a consulta do estado mais recente pode ser realizada de forma eficiente a partir do módulo compilado nativamente:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    SELECT ProductId, LocationID, Quantity, ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId
END;
GO

EXEC [dbo].[spQueryInventoryLatestState];

Analisar as alterações de dados ao longo do tempo torna-se fácil com a cláusula FOR SYSTEM_TIME ALL, conforme mostrado no exemplo a seguir:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory
AS
SELECT ProductId,
    LocationId,
    Quantity,
    ValidFrom,
    ValidTo
FROM [dbo].[ProductInventory]
FOR SYSTEM_TIME ALL;
GO

SELECT * FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

O diagrama a seguir mostra o histórico de dados de um produto que pode ser facilmente renderizado importando o modo de exibição anterior no Power Query, Power BI ou ferramenta de business intelligence semelhante:

As tabelas temporais podem ser usadas neste cenário para realizar outros tipos de análise de viagem no tempo, como reconstruir o estado do inventário AS OF qualquer ponto no tempo no passado ou comparar instantâneos que pertencem a diferentes momentos no tempo.

Para esse cenário de uso, você também pode estender as tabelas Produto e Localização para se tornarem tabelas temporais para permitir a análise posterior do histórico de alterações de UnitPrice e NumberOfEmployee.

ALTER TABLE Product ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location] ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DFValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE [Location]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Como o modelo de dados agora envolve várias tabelas temporais, a prática recomendada para AS OF análise é criar uma exibição que extraia os dados necessários das tabelas relacionadas e aplique FOR SYSTEM_TIME AS OF à exibição, pois isso simplifica muito a reconstrução do estado de todo o modelo de dados:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
    PrInv.LocationId,
    P.ProductName,
    L.LocationName,
    PrInv.Quantity,
    P.UnitPrice,
    L.NumberOfEmployees,
    P.ValidFrom AS ProductStartTime,
    P.ValidTo AS ProductEndTime,
    L.ValidFrom AS LocationStartTime,
    L.ValidTo AS LocationEndTime,
    PrInv.ValidFrom AS InventoryStartTime,
    PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
INNER JOIN dbo.Product AS P
    ON PrInv.ProductId = P.ProductID
INNER JOIN dbo.Location AS L
    ON PrInv.LocationId = L.LocationID;
GO

SELECT * FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF '2022-01-01';

A captura de tela a seguir mostra o plano de execução gerado para a consulta SELECT. Isso ilustra que o Mecanismo de Banco de Dados lida com toda a complexidade ao lidar com relações temporais:

Use o código a seguir para comparar o estado do estoque de produtos entre dois pontos no tempo (um dia atrás e um mês atrás):

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo DATETIME2 = DATEADD (MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
    inventoryDayAgo.ProductName,
    inventoryDayAgo.LocationName,
    inventoryDayAgo.Quantity AS QuantityDayAgo,
    inventoryMonthAgo.Quantity AS QuantityMonthAgo,
    inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
    inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
INNER JOIN vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Deteção de anomalias

Deteção de anomalias (ou deteção de valores atípicos) é a identificação de itens que não estão em conformidade com um padrão esperado ou outros itens em um conjunto de dados. Você pode usar tabelas temporais versionadas do sistema para detetar anomalias que ocorrem periodicamente ou irregularmente, pois pode utilizar consultas temporais para localizar rapidamente padrões específicos. O que é anomalia depende do tipo de dados que você coleta e da sua lógica de negócios.

O exemplo a seguir mostra uma lógica simplificada para detetar "picos" nos números de vendas. Vamos supor que você trabalhe com uma tabela temporal que coleta o histórico dos produtos adquiridos:

CREATE TABLE [dbo].[Product] (
    [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] [varchar](100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

O diagrama a seguir mostra as compras ao longo do tempo:

Assumindo que durante os dias normais o número de produtos comprados tem uma pequena variância, a seguinte consulta identifica outliers singulares: amostras cuja diferença em relação aos seus vizinhos imediatos é duas vezes maior, enquanto as amostras circundantes não diferem significativamente (menos de 20%).

WITH CTE (
    ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo
    )
AS (
    SELECT ProdId,
        LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
        DailySales,
        LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
        ValidFrom,
        ValidTo
    FROM Product
    FOR SYSTEM_TIME ALL
    )
SELECT ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo,
    ABS(PrevValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN NextValue > PrevValue THEN PrevValue
                ELSE NextValue
            END)) AS PrevToNextDiff,
    ABS(CurrentValue - PrevValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > PrevValue THEN PrevValue
                ELSE CurrentValue
            END)) AS CurrentToPrevDiff,
    ABS(CurrentValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > NextValue THEN NextValue
                ELSE CurrentValue
            END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (
        CASE
            WHEN NextValue > PrevValue THEN PrevValue
            ELSE NextValue
        END) < 0.2
    AND ABS(CurrentValue - PrevValue) / (
        CASE
            WHEN CurrentValue > PrevValue THEN PrevValue
            ELSE CurrentValue
        END) > 2
    AND ABS(CurrentValue - NextValue) / (
        CASE
            WHEN CurrentValue > NextValue THEN NextValue
            ELSE CurrentValue
        END) > 2;

Observação

Este exemplo é intencionalmente simplificado. Nos cenários de produção, você provavelmente usaria métodos estatísticos avançados para identificar amostras que não seguem o padrão comum.

Dimensões em mudança lenta

As dimensões no data warehousing normalmente contêm dados relativamente estáticos sobre entidades, como localizações geográficas, clientes ou produtos. No entanto, alguns cenários exigem que você controle as alterações de dados em tabelas de dimensão também. Dado que as modificações nas dimensões acontecem com muito menos frequência, de forma imprevisível e fora do cronograma de atualização regular que se aplica às tabelas de fatos, esses tipos de tabelas de dimensões são chamados de dimensões de mudança lenta (SCD).

Existem várias categorias de dimensões que mudam lentamente com base em como o histórico de mudanças é preservado:

Tipo de dimensão	Detalhes
Tipo 0	A história não é preservada. Os atributos de dimensão refletem valores originais.
Tipo 1	Os atributos de dimensão refletem os valores mais recentes (os valores anteriores são substituídos)
Tipo 2	Cada versão do membro da dimensão representada com uma linha separada na tabela, geralmente com colunas que representam o período de validade
Tipo 3	Mantendo histórico limitado para atributos selecionados usando colunas extras na mesma linha
Tipo 4	Manter o histórico numa tabela separada enquanto a tabela original de dimensão mantém as versões mais recentes (atuais) dos membros da dimensão.

Quando você escolhe uma estratégia SCD, é responsabilidade da camada ETL (Extract-Transform-Load) manter as tabelas de dimensões precisas, o que geralmente requer um código mais complexo e manutenção extra.

As tabelas temporais com versão do sistema podem ser usadas para reduzir drasticamente a complexidade do seu código, pois o histórico de dados é preservado automaticamente. Dada a sua implementação utilizando dois quadros, os quadros temporais estão mais próximos do SCD de Tipo 4. No entanto, como as consultas temporais permitem que você faça referência apenas à tabela atual, você também pode considerar tabelas temporais em ambientes onde planeja usar o SCD Tipo 2.

Para converter a sua dimensão regular em SCD, pode criar uma nova ou alterar uma existente para se tornar uma tabela temporal versionada por sistema. Se a tabela de dimensões existente contiver dados históricos, crie uma tabela separada e mova os dados históricos para lá e mantenha as versões de dimensão atuais (reais) na tabela de dimensões original. Em seguida, use a sintaxe ALTER TABLE para converter a sua tabela de dimensões numa tabela temporal com versão do sistema e com uma tabela de histórico previamente definida.

O exemplo a seguir ilustra o processo e assume que a tabela de dimensão DimLocation já tem ValidFrom e ValidTo como datetime2 colunas não anuláveis, que são preenchidas pelo processo ETL:

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Nenhum código extra é necessário para manter o SCD durante o processo de carregamento do data warehouse, uma vez que o tenha criado.

A ilustração a seguir mostra como você pode usar tabelas temporais em um cenário simples envolvendo dois SCDs (DimLocation e DimProduct) e uma tabela de fatos.

Para utilizar SCDs anteriores em relatórios, é necessário ajustar eficazmente a consulta. Por exemplo, talvez você queira calcular o valor total das vendas e o número médio de produtos vendidos per capita nos últimos seis meses. Ambas as métricas requerem a correlação de dados da tabela de fatos e dimensões que podem ter alterado seus atributos importantes para a análise (DimLocation.NumOfCustomers, DimProduct.UnitPrice). A consulta a seguir calcula corretamente as métricas necessárias:

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo DATETIME2;

SET @sixMonthsAgo = DATEADD(month, - 12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
    DimLocation_History.LocationId,
    SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
    AVG(F.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimLocation
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
    ON DimLocation_History.LocationId = F.LocationId
    AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimProduct
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
    ON DimProduct_History.ProductId = F.ProductId
    AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Considerações

O uso de tabelas temporais com versionamento do sistema para SCD é aceitável se o período de validade calculado com base no tempo de transação do banco de dados estiver de acordo com a sua lógica de negócios. Se você carregar dados com atraso significativo, o tempo de transação pode não ser aceitável.

Por padrão, as tabelas temporais com versão do sistema não permitem alterar dados históricos após o carregamento (você pode modificar o histórico depois de definir SYSTEM_VERSIONING como OFF). Esta pode ser uma limitação nos casos em que a alteração de dados históricos acontece regularmente.

As tabelas versionadas por sistema temporal geram a versão da linha sempre que há uma alteração em qualquer coluna. Se você quiser suprimir novas versões em determinadas alterações de coluna, precisará incorporar essa limitação na lógica ETL.

Se você espera um número significativo de linhas históricas em tabelas SCD, considere usar um índice columnstore clusterizado como a principal opção de armazenamento para a tabela de histórico. O uso de um índice columnstore reduz o espaço ocupado pela tabela de histórico e acelera suas consultas analíticas.

Reparar corrupção de dados em nível de linha

Você pode confiar em dados históricos em tabelas temporais versionadas pelo sistema para reparar rapidamente linhas individuais em qualquer um dos estados capturados anteriormente. Essa propriedade de tabelas temporais é útil quando você consegue localizar linhas afetadas e/ou quando sabe a hora da alteração indesejada dos dados. Esse conhecimento permite que você execute reparos de forma eficiente sem lidar com backups.

Esta abordagem tem várias vantagens:

• Você pode controlar o escopo do reparo com precisão. Os registros que não são afetados precisam permanecer no estado mais recente, o que geralmente é um requisito crítico.

• A operação é eficiente e o banco de dados permanece on-line para todas as cargas de trabalho usando os dados.

• A operação de reparo em si é versionada. Você tem trilha de auditoria para a operação de reparo em si, para que possa analisar o que aconteceu mais tarde, se necessário.

A ação de reparo pode ser automatizada com relativa facilidade. O próximo exemplo de código mostra um procedimento armazenado que executa o reparo de dados para a tabela Employee usada em um cenário de auditoria de dados.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord
    @EmployeeID INT,
    @versionNumber INT = 1
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmployeeID
            ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID
)
/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

Este procedimento armazenado recebe @EmployeeID e @versionNumber como parâmetros de entrada. Este procedimento, por padrão, restaura o estado da linha para a última versão do histórico (@versionNumber = 1).

A imagem seguinte mostra o estado da linha antes e depois da chamada do procedimento. O retângulo vermelho marca a versão atual da linha que está incorreta, enquanto o retângulo verde marca a versão correta do histórico.

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1;

Este procedimento armazenado de reparo pode ser definido para aceitar um carimbo de data/hora exato em vez de versão de linha. Ele restaura a linha para qualquer versão que estava ativa para o point-in-time fornecido (ou seja, AS OF point-in-time).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf
    @EmployeeID INT,
    @asOf DATETIME2
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
SET [Position] = History.[Position],
    [Department] = History.Department,
    [Address] = History.[Address],
    AnnualSalary = History.AnnualSalary
FROM Employee AS E
INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
    ON E.EmployeeID = History.EmployeeID
WHERE E.EmployeeID = @EmployeeID;

Para a mesma amostra de dados, a imagem a seguir ilustra um cenário de reparo com uma condição de tempo. Destacam-se o parâmetro @asOf, a linha selecionada no histórico que era real no momento fornecido e a nova versão da linha na tabela atual após a operação de reparo:

A correção de dados pode tornar-se parte do carregamento automatizado de dados em sistemas de armazenamento de dados e relatórios. Se um valor recém-atualizado não estiver correto, em muitos cenários, restaurar a versão anterior do histórico é uma atenuação boa o suficiente. O diagrama a seguir mostra como esse processo pode ser automatizado:

Conteúdo relacionado

• Tabelas temporais
• Comece com tabelas temporais com versão gerida 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
• de 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