Partilhar via

Criar uma tabela temporal com controle de versão pelo sistema

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Há três maneiras de criar uma tabela temporal com controle da versão do sistema ao considerar o modo como a tabela de histórico é especificada:

  • Tabela temporal com uma tabela de histórico anônimo: especifique o esquema da tabela atual e deixe o sistema criar a tabela de histórico correspondente com o nome gerado automaticamente.

  • Tabela temporal com uma tabela de histórico padrão: especifique o nome do esquema de tabela de histórico e o nome da tabela e deixe o sistema criar tabela de histórico nesse esquema.

  • Tabela temporal com uma tabela de histórico definida pelo usuário criada antecipadamente: crie a tabela de histórico que melhor atenda às suas necessidades e faça referência a essa tabela durante a criação da tabela temporal.

Criar uma tabela temporal com uma tabela de histórico anônimo

Criar uma tabela temporal com uma tabela de histórico "anônimo" é uma opção conveniente para a criação rápida de objeto, especialmente em ambientes de teste e de protótipos. Também é a maneira mais simples de criar uma tabela temporal, já que não exige qualquer parâmetro na cláusula SYSTEM_VERSIONING. No exemplo a seguir, uma nova tabela é criada com o controle de versão do sistema habilitado, sem definir o nome da tabela de histórico.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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);

Comentários

  • Uma tabela temporal com controle da versão do sistema deve ter uma chave primária definida e ter exatamente um PERIOD FOR SYSTEM_TIME definido com duas colunas datetime2, declaradas como GENERATED ALWAYS AS ROW START ou GENERATED ALWAYS AS ROW END.

  • Supõe-se que as colunas PERIOD sempre são não anuláveis, mesmo se a nulidade não é especificada. Quando as colunas PERIOD são definidas explicitamente como anuláveis, a instrução CREATE TABLE falha.

  • A tabela de histórico deve sempre ser alinhada ao esquema com a tabela atual ou temporal, em relação ao número de colunas, nomes de coluna, ordenação e tipos de dados.

  • Uma tabela de histórico anônimo é criada automaticamente no mesmo esquema que a tabela temporal ou atual.

  • O nome da tabela de histórico anônima tem o seguinte formato: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[sufixo]. O sufixo é opcional e adicionado somente se a primeira parte do nome da tabela não for exclusiva.

  • A tabela de histórico é criada como uma tabela rowstore. Se possível, a compactação de PÁGINA é aplicada, caso contrário, a tabela de histórico é descompactada. Por exemplo, algumas configurações de tabela, como as colunas ESPARSAS, não permitem a compactação.

  • Um índice clusterizado padrão é criado para a tabela de histórico com um nome gerado automaticamente no formato IX_<history_table_name>. O índice clusterizado contém as colunas PERIOD (início, fim).

  • Para criar a tabela atual como uma tabela com otimização de memória, consulte Tabelas temporais com controle da versão do sistema com tabelas com otimização de memória.

Criar uma tabela temporal com uma tabela de histórico padrão

A criação de uma tabela temporal com uma tabela de histórico padrão é uma opção conveniente quando você deseja controlar a nomenclatura e ainda depende do sistema para criar a tabela de histórico com a configuração padrão. No exemplo a seguir, uma nova tabela é criada com o controle de versão do sistema habilitado, com o nome da tabela de histórico definido explicitamente.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

Comentários

A tabela de histórico é criada usando as mesmas regras que se aplicam à criação de uma tabela de histórico "anônimo", com as seguintes regras se aplicam especificamente à tabela de histórico nomeada.

  • O nome do esquema é obrigatório para o parâmetro HISTORY_TABLE.
  • Quando o esquema especificado não existe, a instrução CREATE TABLE falha.
  • Quando a tabela especificada pelo parâmetro HISTORY_TABLE já existe, ela é validada em relação à tabela temporal recém-criada em termos de consistência do esquema e consistência dos dados temporais. Quando você especifica uma tabela de histórico inválido, a instrução CREATE TABLE falha.

Criar uma tabela temporal com uma tabela de histórico definida pelo usuário

A criação de uma tabela temporal com uma tabela de histórico definida pelo usuário é uma opção conveniente quando o usuário deseja especificar a tabela de histórico com opções de armazenamento específicas e índices diferentes ajustados às consultas históricas. No exemplo a seguir, uma tabela de histórico definido pelo usuário é criada com um esquema alinhado à tabela temporal criada. Para esta tabela de histórico definida pelo usuário, um índice columnstore clusterizado e um índice rowstore não clusterizado adicional (árvore B+) são criados para pesquisas de ponto. Após a criação dessa tabela de histórico definido pelo usuário, a tabela temporal com controle da versão do sistema é criada especificando a tabela de histórico definido pelo usuário como a tabela de histórico padrão.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns ON DepartmentHistory (
    ValidTo,
    ValidFrom,
    DeptID
    );
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

Comentários

  • Se você planeja executar consultas analíticas em dados históricos que empregam agregações ou funções de janelamento, a criação de uma columnstore clusterizada como um índice primário é altamente recomendável para o desempenho da consulta e compactação.
  • Se o caso de uso principal for a auditoria de dados (ou seja, pesquisa por alterações históricas de uma única linha da tabela atual), uma boa opção será criar uma tabela de histórico de rowstore com um índice clusterizado
  • A tabela de histórico não pode ter chave primária, chaves estrangeiras, índices exclusivos, restrições de tabela ou gatilhos. Ela não pode ser configurada para captura de dados de alterações, controle de alterações ou replicação de mesclagem ou transacional.

Alteração de tabela não temporal para tabela temporal com controle da versão do sistema

É possível habilitar o controle da versão do sistema usando uma tabela não temporal existente, por exemplo, quando você deseja migrar uma solução personalizada temporal para o suporte interno. Por exemplo, você pode ter um conjunto de tabelas nas quais o controle de versão é implementado com gatilhos. O uso do controle de versão do sistema temporal é menos complexo e oferece outros benefícios que incluem:

  • Histórico imutável
  • Nova sintaxe para consultas entre períodos
  • Melhor desempenho de DML
  • Custos mínimos de manutenção

Ao converter uma tabela existente, considere o uso da cláusula HIDDEN para ocultar as novas colunas PERIOD (as colunas datetime2 ValidFrom e ValidTo), a fim de evitar o impacto em aplicativos existentes que não especificam explicitamente os nomes das colunas (por exemplo, SELECT * ou INSERT sem lista de colunas) e não foram projetados para lidar com novas colunas.

Adição do controle de versão a tabelas não temporais

Para controlar as alterações de uma tabela não temporal que contém os dados, é necessário adicionar a definição PERIOD e, opcionalmente, fornecer um nome para a tabela de histórico vazio que o SQL Server cria para você:

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy
    ADD
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Importante

A precisão de DATETIME2 deve alinhar-se à precisão da tabela subjacente. Confira os comentários a seguir.

Comentários

  • A adição de colunas não nulas com padrões à tabela com dados existente é uma operação de tamanho de dados (size of data operation) em todas as edições com exceção do SQL Server Enterprise (em que é uma operação de metadados). Com uma tabela de histórico grande e com dados existente no SQL Server Standard, a adição de uma coluna não nula pode ser uma operação dispendiosa.
  • As restrições para colunas com período de início e período de término devem ser escolhidas com cuidado:
    • O padrão para a coluna inicial especifica a partir de qual ponto no tempo você considera as linhas existentes válidas. Isso não pode ser especificado como um ponto de datetime no futuro.
    • A hora de término precisa ser especificada como o valor máximo para determinada precisão de datetime2, por exemplo, 9999-12-31 23:59:59 ou 9999-12-31 23:59:59.9999999.
  • A adição de PERIOD executará a verificação de consistência dos dados na tabela atual a fim de se certificar de que os valores existentes para colunas do período sejam válidos.
  • Quando uma tabela de histórico existente é especificada ao habilitar SYSTEM_VERSIONING, uma verificação de consistência de dados temporais é executada na tabela atual e de histórico. Ela pode ser ignorada se você especificar DATA_CONSISTENCY_CHECK = OFF como um parâmetro adicional.

Migrar as tabelas existentes para o suporte interno

Este exemplo mostra como migrar de uma solução existente com base em gatilhos para o suporte temporal interno. Para este exemplo, vamos supor que a solução personalizada atual divide os dados atuais e históricos em duas tabelas de usuário separadas (ProjectTaskCurrent e ProjectTaskHistory).

Se a solução existente usa uma só tabela para armazenar as linhas reais e históricas, divida os dados entre duas tabelas antes das etapas de migração mostradas no exemplo a seguir. Primeiro, solte o gatilho na tabela temporal futura. Em seguida, verifique se as colunas PERIOD não são anuláveis.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Comentários

  • A referência às colunas existentes na definição de PERIOD altera implicitamente generated_always_type para AS_ROW_START e para AS_ROW_END nessas colunas.
  • A adição de PERIOD executará a verificação de consistência dos dados na tabela atual a fim de se certificar de que os valores existentes para colunas do período sejam válidos
  • É altamente recomendável definir SYSTEM_VERSIONING com DATA_CONSISTENCY_CHECK = ON para impor as verificações de consistência de dados nos dados existentes.
  • Se as colunas ocultas forem preferenciais, use o comando ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Próximas etapas