Como usar a tabela temporal do SQL Server

Tabelas temporais, também conhecidas como tabelas versionadas pelo sistema, são tabelas que permitem rastrear e manter um histórico dos dados em uma tabela específica. Usando tabelas temporais, você pode rastrear o histórico das alterações feitas nos dados em uma tabela.

Este artigo estabelecerá uma base para criar, trabalhar e usar tabelas temporais no SQL Server.

As tabelas de versão do sistema foram introduzidas no padrão ANSI SQL 2011 e estão disponíveis como um recurso no SQL Server 2016 e acima.

Ao contrário de uma tabela normal que só pode mostrar e trabalhar com dados atuais, as tabelas temporais permitem visualizar e trabalhar mesmo com dados excluídos anteriormente. Como mencionado, isso é possível devido à capacidade de uma tabela temporal de acompanhar as alterações feitas nos dados em uma tabela.

A tabela contém duas colunas principais: SysStartTime e Sysendtime. Essas duas colunas são usadas para definir os dados existentes e anteriores para cada registro em uma tabela. Você pode usar intervalos de tempo específicos para ver como os dados em uma tabela mudaram.

Crie uma tabela temporal

Antes de criar uma tabela temporal, ela deve atender aos seguintes requisitos:

1. Uma tabela temporal deve conter uma restrição de chave primária definida.
2. Ele deve conter duas colunas para gravar a data inicial e de término. Essas colunas devem ser do tipo de dados DATETime2. As colunas devem ser declaradas como geradas sempre como iniciações de linha/final.
3. O SQL Server assume que as duas colunas não são indicáveis. Portanto, a instrução CREATE TABLE falha se a consulta tentar definir colunas que sejam anuláveis.
4. O SQL Server gera automaticamente uma tabela de histórico usando um esquema semelhante ao da tabela temporal.
5. Você não pode usar em vez de gatilhos em uma tabela de versão do sistema.
6. A tabela de história não deve conter nenhuma restrição.
7. Você não pode alterar os dados na tabela de histórico.
8. Declarações, como inserção e atualização, não podem fazer referência às colunas do período.
9. A tabela de história é criada como uma tabela de histórico de linhas e a compactação da página é aplicada se aplicável. Caso contrário, a tabela é deixada sem comprovação.
10. O SQL Server irá gerar automaticamente um índice agrupado para a tabela de histórico.

Como criar uma tabela temporal: T-SQL

Vejamos uma demonstração simples de criar uma tabela temporal. Considere a consulta de exemplo mostrada abaixo:

Crie a tabela DBO.my_temporal_table (
eu não fiz,
fname Varchar (50),
Email Varchar (255),
Departamento Varchar (50),
Restrição PK Primária Chave (ID),
Sysstarttime datetime2 gerado sempre como linha iniciante não nula,
Sysendtime datetime2 gerado sempre como final de linha não nula,
período para system_time (sysstarttime, sysendtime)) com (system_versioning = on);

Depois de executar a consulta acima, o servidor SQL criará a tabela com o nome especificado.

No SQL Server Management Studio, você pode visualizar uma tabela de versão do sistema expandindo a opção Tabelas no seu banco de dados de destino:

Observe que o SQL Server gera automaticamente uma tabela de histórico com um esquema semelhante à tabela de versão do sistema. No entanto, preste atenção às colunas na tabela de história. Observe que eles não têm nenhuma restrição.

Considere a imagem mostrada abaixo:

Como você verá, o SQL Server gera uma tabela de histórico sob um nome após um formato específico. Para definir um nome personalizado para sua tabela de histórico, especifique -o na instrução CREATE TABLE como mostrado:

---
período para system_time (sysstarttime, sysendtime)) com (system_versioning = on, history_table = mytemporal_tablehistory);
---

Em seguida, se você expandir a opção Indexos para a tabela de histórico, perceberá que o SQL Server gerou automaticamente um índice em cluster:

Usando tabelas temporais

Vamos testar a funcionalidade das tabelas temporais inserindo alguns registros na tabela. Considere a consulta de exemplo mostrada abaixo:

Insira em my_temporal_table (id, fname, email, departamento)
Valores (1, 'John Davis', '[email protected] ',' Front-end '),
(2, 'Ruby Raw', '[email protected] ',' banco de dados '),
(3, 'Scott Turner', '[email protected] ',' Full Stack '),
(4, 'Alice Jensen', '[email protected] ',' controle de versão '),
(5, 'Peter Green', '[email protected] ',' back -end ');

Depois que tivermos os dados de amostra inseridos na tabela, podemos consultar como:

Selecione * de my_temporal_table;

Você deve obter uma saída próxima à mostrada abaixo como

Para entender como funciona a tabela de versões do sistema, vamos excluir e atualizar linhas na tabela:

Excluir de my_temporal_table where department = 'banco de dados';
Atualizar my_temporal_table set fname = 'John m' where id = 5;

Em seguida, consulte os dados na tabela principal:

Selecione * de my_temporal_table;

Se você consultar a tabela de histórico, verá a versão antiga dos dados com os registros de data e hora corretos.

Conclusão

Este guia abrangeu o conceito de tabela temporal ou de versão do sistema no SQL Server. Usando este guia, você estará em posição de rastrear o histórico de seus dados usando tabelas temporais do SQL Server. Esperamos que você tenha achado este artigo útil. Confira mais artigos de dica do Linux para obter dicas e tutoriais.