Partilhar via

Criar uma tabela temporal com versão do sistema

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

Há três maneiras de criar uma tabela temporal versionada pelo sistema considerando como a tabela de histórico é especificada.

  • Tabela temporal com uma tabela de histórico anônima: você especifica o esquema da tabela atual e permite que o sistema crie uma tabela de histórico correspondente com 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 maneira mais simples de criar uma tabela temporal, uma vez que não requer nenhum 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 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.

Uma tabela de histórico anônima é criada automaticamente 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.

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 (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. 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ô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 tabela de histórico de definida pelo utilizador é uma opção conveniente quando o utilizador deseja especificar uma tabela de histórico com opções de armazenamento específicas e índices diferentes ajustados para 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 utilizador, são criados um índice de colunas clusterizado e um índice em árvore B não clusterizado adicional para consultas pontuais. Depois que essa tabela de histórico definida pelo usuário é criada, a tabela temporal é criada especificando a tabela de histórico definida pelo usuário como a tabela de histórico padrão.

Observação

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

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

Caso planeie executar consultas analíticas sobre dados históricos que utilizem agregações ou funções de janela, a criação de um armazenamento por colunas em cluster usado como índice primário é altamente recomendada para compactação e 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

Você pode habilitar o controle de versão do sistema em uma tabela não temporal existente, como quando deseja migrar uma solução temporal personalizada para o suporte interno.

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 cláusula HIDDEN para ocultar as novas colunas de PERIOD (as colunas datetime2ValidFrom e ValidTo) para evitar afetar os aplicativos existentes que não especificam explicitamente nomes de colunas, como é o caso de SELECT * ou INSERT sem uma lista de colunas, pois esses aplicativos não foram projetados para lidar com colunas novas.

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.

Comentários

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 executa uma verificação de consistência de dados na tabela atual para certificar-se de que os valores existentes para colunas de 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. Neste exemplo, assumimos que a solução personalizada atual divide os dados atuais e históricos em duas tabelas de usuário separadas (ProjectTaskCurrent e ProjectTaskHistory).

Se sua solução existente usa uma única tabela para armazenar linhas reais e históricas, você deve dividir os dados em 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

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 executa uma verificação de consistência de dados na tabela atual para certificar-se de que os valores existentes para colunas de 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 preferir colunas ocultas, use o comando ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Conteúdo relacionado