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

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstâ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, pois ela não requer nenhum parâmetro na SYSTEM_VERSIONING cláusula . 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 versão do sistema deve ter uma chave primária definida e ter exatamente uma PERIOD FOR SYSTEM_TIME definida com duas colunas datetime2 , declaradas como GENERATED ALWAYS AS ROW START ou GENERATED ALWAYS AS ROW END.

  • As PERIOD colunas sempre são consideradas não anuláveis, mesmo que a nulidade não seja especificada. Se as PERIOD colunas forem definidas explicitamente como anuláveis, a CREATE TABLE instrução falhará.

  • A tabela de histórico sempre deve estar alinhada ao esquema com a tabela atual ou temporal, em relação ao número de colunas, nomes de colunas, 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. A compactação PAGE é aplicada, se possível, 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 PERIOD colunas (end, start).

  • 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 explicitamente definido.

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 HISTORY_TABLE parâmetro .
  • Se o esquema especificado não existir, a CREATE TABLE instrução falhará.
  • Se a tabela especificada pelo HISTORY_TABLE parâmetro já existir, ela será validada em relação à tabela temporal recém-criada em termos de consistência de esquema e consistência de dados temporais. Se você especificar uma tabela de histórico inválida, a CREATE TABLE instrução falhará.

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 definida pelo usuário é criada com um esquema alinhado com a 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

Você pode habilitar o controle de versão do sistema em uma tabela não temporal existente, como quando você deseja migrar uma solução temporal personalizada para 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 usar a HIDDEN cláusula para ocultar as novas PERIOD colunas (as colunas ValidFromdatetime2 e ValidTo) para evitar afetar aplicativos existentes que não especificam explicitamente nomes de coluna (por exemplo, SELECT * ou INSERT sem lista de colunas) não foram projetados para lidar com novas colunas.

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

Se você quiser começar a controlar as alterações de uma tabela não temporal que contenha os dados, precisará adicionar a PERIOD definição e, opcionalmente, fornecer um nome para a tabela de histórico vazia que 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 para DATETIME2 deve se alinhar com a precisão da tabela subjacente. Confira as observações a seguir.

Comentários

  • Adicionar colunas não anuláveis com padrões a uma tabela existente com dados é um tamanho de operação de dados em todas as edições que não sejam SQL Server Enterprise edição (na qual é uma operação de metadados). Com uma grande tabela de histórico existente com dados em SQL Server Standard edição, adicionar uma coluna não nula pode ser uma operação cara.
  • 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.
  • Adicionar PERIOD executa uma consistência de dados marcar na tabela atual para garantir que os valores existentes para colunas de período sejam válidos.
  • Quando uma tabela de histórico existente é especificada ao habilitar SYSTEM_VERSIONING, uma consistência de dados marcar é executada na tabela atual e no histórico. Ele poderá ser ignorado 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 PERIOD colunas são nã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

  • Referenciar colunas existentes na PERIOD definição muda generated_always_type implicitamente para AS_ROW_START e AS_ROW_END para essas colunas.
  • Adicionar PERIOD executa um marcar de consistência de dados na tabela atual para garantir que os valores existentes para colunas de período sejam válidos
  • É altamente recomendável definir SYSTEM_VERSIONING com DATA_CONSISTENCY_CHECK = ON para impor verificações de consistência de dados em dados existentes.
  • Se as colunas ocultas forem preferenciais, use o comando ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Próximas etapas