Criar uma tabela temporal com versão do 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

Pode criar uma tabela temporal com versão de sistema de três formas, consoante a forma como especifica a tabela de histórico:

  • Tabela temporal com uma tabela de história anónima: especifica o esquema da tabela atual e deixa o sistema criar uma tabela de histórico correspondente com um nome gerado automaticamente.

  • Tabela temporal com uma tabela de histórico padrão : você especifica o nome do esquema da tabela de histórico e o nome da tabela e permite que o sistema crie uma tabela de histórico nesse esquema.

  • A tabela temporal com uma tabela de histórico definida pelo usuário criada previamente: você cria uma tabela de histórico que melhor atende às suas necessidades e, em seguida, faz referência a essa tabela durante a criação da tabela temporal.

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

Criar uma tabela temporal com uma tabela de histórico de anônima é uma opção conveniente para a criação rápida de objetos, especialmente em protótipos e ambientes de teste. É também a forma mais simples de criar uma tabela temporal porque não requer nenhum parâmetro na SYSTEM_VERSIONING cláusula. O exemplo seguinte cria uma nova tabela com a versão do sistema ativada, 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
);

Remarks

Uma tabela temporal versionada pelo 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 colunas PERIOD são sempre consideradas não anuláveis, mesmo que a anulabilidade não seja especificada. Se as colunas PERIOD forem explicitamente definidas como anuláveis, a instrução CREATE TABLE falhará.

A tabela de histórico deve estar sempre alinhada com a tabela atual ou temporal, no que diz respeito ao número de colunas, nomes de colunas, ordem e tipos de dados.

O Database Engine cria automaticamente uma tabela de histórico anónima no mesmo esquema da tabela atual ou temporal.

O nome da tabela de histórico anônimo tem o seguinte formato: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. 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. PAGE compactação é aplicada, se possível, caso contrário, a tabela de histórico é descompactada. Por exemplo, algumas configurações de tabela, como SPARSE colunas, não permitem compactação.

É criado um índice clusterizado por defeito 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 (fim, início).

No banco de dados SQL do Fabric, a tabela de histórico criada não é replicada no Fabric OneLake.

Para criar a tabela atual como uma tabela otimizada para memória, consulte Tabelas temporais com versão do sistema com tabelas otimizadas para memória.

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

Criar uma tabela temporal com uma tabela de histórico padrão é uma opção conveniente quando pretende controlar a nomenclatura e ainda conta com o sistema para criar a tabela de histórico com a configuração padrão. O exemplo seguinte cria uma nova tabela com a versão do sistema ativada, 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
    )
);

Remarks

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

  • O nome do esquema é obrigatório para o parâmetro HISTORY_TABLE.

  • Se o esquema especificado não existir, a instrução CREATE TABLE falhará.

  • Se a tabela especificada pelo parâmetro HISTORY_TABLE 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 instrução CREATE TABLE falhará.

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

Criar uma tabela temporal com uma tabela de histórico definida pelo utilizador é uma opção conveniente quando se quer especificar uma tabela de histórico com opções de armazenamento específicas e diferentes índices ajustados a consultas históricas. No exemplo seguinte, cria-se uma tabela de histórico definida pelo utilizador com um esquema alinhado com a tabela temporal. Esta tabela de histórico tem um índice columnstore em cluster e um índice rowstore não clusterizado adicional (árvore B) para procuras pontuais. Depois de criares a tabela de histórico, crias a tabela temporal e especificas a tabela de histórico definida pelo utilizador como a tabela de histórico predefinida.

Note

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 de armazenamento em colunas ou a índices em tabelas com otimização de memória. Para mais informações, consulte o guia de arquitetura e estrutura de índices do SQL Server e do SQL do Azure.

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
    )
);

Remarks

Se planeia executar consultas analíticas sobre os dados históricos que utilizam agregados ou funções de janela, é altamente recomendável criar um índice columnstore agrupado como índice primário para melhorar a compressão e o desempenho das consultas.

Se você planeja usar tabelas temporais para auditoria de dados (ou seja, pesquisar alterações históricas para uma única linha da tabela atual), crie uma tabela de histórico de armazenamento de linhas com um índice clusterizado.

A tabela de histórico não pode ter uma chave primária, chaves estrangeiras, índices exclusivos, restrições de tabela ou gatilhos. Ele não pode ser configurado para captura de dados de alteração, controle de alterações, replicação transacional ou replicação de mesclagem.

No Banco de Dados SQL do Fabric e no Banco de Dados SQL do Azure com espelhamento do Fabric configurado, quando utiliza uma tabela existente como a tabela histórica durante a criação da tabela temporal, a tabela existente deixa de ser espelhada.

Alterar tabela não temporal existente para ser uma tabela temporal com versão controlada pelo sistema

Pode ativar a versão do sistema numa tabela não temporal existente, como quando quer migrar uma solução temporal personalizada para suporte incorporado.

Por exemplo, você pode ter um conjunto de tabelas em que o controle de versão é implementado com gatilhos. O uso do controle de versão temporal do sistema é menos complexo e oferece outros benefícios, incluindo:

  • História imutável
  • Nova sintaxe para consultas de viagem no tempo
  • Melhor desempenho DML
  • Custos de manutenção mínimos

Ao converter uma tabela existente, considere usar a HIDDEN cláusula para ocultar as novas PERIOD colunas (as colunas datetime2ValidFrom e ValidTo) para evitar afetar aplicações existentes que não especificam explicitamente nomes de colunas (por exemplo, SELECT * ou INSERT sem uma lista de colunas) e que não são concebidas para lidar com novas colunas.

Adicionar controle de versão a tabelas não temporais

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

Remarks

Adicionar colunas não anuláveis com valores padrão a uma tabela existente com dados é uma operação de tamanho de dados em todas as edições diferentes da edição SQL Server Enterprise (na qual é uma operação de metadados). Com uma grande tabela de histórico existente com dados na edição SQL Server Standard, adicionar uma coluna não nula pode ser uma operação cara.

As restrições para as colunas de início e fim de período devem ser cuidadosamente escolhidas:

  • Padrão para a coluna inicial especifica a partir de qual momento você considera as linhas existentes válidas. Não pode ser especificado como uma data e hora no futuro.

  • A hora de término deve ser especificada como o valor máximo para uma determinada precisão de datetime2, tais como 9999-12-31 23:59:59 ou 9999-12-31 23:59:59.9999999.

Adicionar PERIOD realiza uma verificação de consistência de dados na tabela atual para garantir que os valores existentes para as colunas do período são válidos.

Quando uma tabela de histórico existente é especificada ao habilitar SYSTEM_VERSIONING, uma verificação de consistência de dados é executada na tabela atual e na tabela de histórico. Ele pode ser ignorado se você especificar DATA_CONSISTENCY_CHECK = OFF como um parâmetro extra.

Migrar tabelas existentes para suporte interno

Este exemplo mostra como migrar de uma solução existente baseada em gatilhos para suporte temporal interno. Este exemplo assume que a solução personalizada atual divide os dados atuais e históricos em duas tabelas de utilizador separadas (ProjectTaskCurrent e ProjectTaskHistory).

Se a sua solução existente usar uma única tabela para armazenar linhas reais e históricas, então deve dividir os dados em duas tabelas antes dos passos de migração mostrados no exemplo seguinte. 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 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
        )
    );

Remarks

Referenciar as colunas existentes na definição de PERIOD altera implicitamente generated_always_type para AS_ROW_START e AS_ROW_END para essas colunas.

Adicionar PERIOD realiza uma verificação de consistência de dados na tabela atual para garantir que os valores existentes para as colunas do período são válidos.

É altamente recomendável que você defina SYSTEM_VERSIONING com DATA_CONSISTENCY_CHECK = ON, para impor verificações de consistência de dados em dados existentes.

Se as colunas ocultas forem preferidas, use o seguinte comando:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Conteúdo relacionado

  • Last updated on