Stack Overflow
Stack Overflow

Questions tagged [temporal-tables]

System-versioned temporal tables are designed to keep a full history of data changes and allow easy point in time analysis. Use this tag with questions related to Temporal tables in SQL Server 2016 or higher. Don't use in case of RDBMS other than SQL Server. Do not use this tag for normal SQL scripts and normal relational tables.

Temporal Tables are Microsoft Sql Server support for system-versioned tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2017.

Supported by Sql Server 2016 (all versions) and Sql Azure v12

DOCUMENTATION

181 questions
26
votes
3 answers

NHibernate HQL Generator to support SQL Server 2016 temporal tables

I am trying to implement basic support for SQL Server 2016 temporal tables in NHibernate 4.x. The idea is to alter SQL statement from SELECT * FROM Table t0 to SELECT * FROM Table FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00' t0 You can find…
veeroo
  • 752
  • 6
  • 25
22
votes
7 answers

How to identify changed values using a SQL Server temporal table?

I have a SQL Azure table and I have turned on the new Temporal Table feature (New to SQL Server 2016 and SQL Azure v12). This feature creates another table to track all changes to the primary table (I included a link to the docs about temporal…
J King
  • 4,108
  • 10
  • 53
  • 103
12
votes
2 answers

Capture the user who deleted the row in Temporal table

I understand that temporal tables are intended to give you a point in time view of the data. I am using temporal tables for auditing purpose. I have the following Temporal table. Lets assume this is the current state of the Temporal table: ID …
LP13
  • 30,567
  • 53
  • 217
  • 400
12
votes
3 answers

Alter Column to Not Null where System Versioned column was nullable

I'm using SQL Server and system-versioned (temporal) tables. In my main table, I have an INT column that's currently allowing NULLs. I want to update this to not allow nulls, but the system/history copy of the table allows nulls. I run this…
jleach
  • 7,410
  • 3
  • 33
  • 60
11
votes
2 answers

Why we require temporal table in SQL Server 2016 as we have CDC or CT?

What advantages do Temporal Tables have over Change Data Capture or Change Tracking in SQL Server?
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
10
votes
0 answers

Why does a merge into a temporal table with a nonclustered index in the history table throw an error

I get the following error when I try to merge under a few conditions. Is someone able to explain why? Seems like it's a problem with SQL Server itself, but I wanted to post it here to confirm. Attempting to set a non-NULL-able column's value to…
CSharpFiasco
  • 204
  • 3
  • 8
9
votes
3 answers

How to Stopping System-Versioning on a System-Versioned Temporal Table in SQL Server 2016?

I have a table that has system versioning (temporal table), but I can not see a design environment visually. I do it because I can see the SYSTEM_VERSIONING clause has been used. I would like to have temporarily Stop and then enable it. Who can…
Aiyoub A.
  • 5,261
  • 8
  • 25
  • 38
9
votes
3 answers

How can alter existence tables to SQL Temporal table by keeping data?

I have many table with data, which I want to convert to Microsoft Temporal table, But when I want to convert temporal table cause lost my data. My code is: Alter TABLE dbo.Employee ( [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED ,…
Hamed Roshangar
  • 150
  • 8
7
votes
5 answers

Entity Framework Core and SQL Server 2016 temporal tables

We are using EF Core and SQL Server 2016 for our .NET Core Web API. I am evaluating use of temporal tables and its impact on EF Core code. When I generate the EF model using cmdline then it generates model with appstart, append and mappings in…
krishnakumar
  • 617
  • 1
  • 6
  • 22
6
votes
1 answer

Date functions in temporal tables

Since SQL Server 2016 it is possible to automatically create Temporal Tables. I wanted to create a simple query that retrieves the data from a specified date. However, when I try to specify a date in the query like so, it gives a syntax error:…
iPhantomGuy
  • 240
  • 1
  • 11
5
votes
1 answer

EF Core 6.0 temporal tables - Add-Migration - Period property 'Comment.PeriodStart' must be a shadow property

We have recently upgraded our project to Microsoft.EntityFrameworkCore 6.0.0. This release enables SQL Server temporal tables out of the…
Ogglas
  • 62,132
  • 37
  • 328
  • 418
5
votes
1 answer

MariaDB - Inserting historical data into a system versioned (temporal) table

I have some tables in MariaDB that I have been tracking the changes for by using a separate "changelog" table that updates every time a record is updated. However I have recently learned about temporal data tables in MariaDB and I would like to…
zeke
  • 3,603
  • 2
  • 26
  • 41
5
votes
3 answers

How to add HIDDEN property on column?

When temporal table is created, we need to defined start and end date time columns which can be hidden - not visible in SELECT * or INSERT without columns. I want to add one more column, which will contain information about the user who has commit…
gotqn
  • 42,737
  • 46
  • 157
  • 243
4
votes
1 answer

How to get ValidFrom and ValidTo columns from temporal tables in Entity Framework Core?

Is there a way to get to ValidFrom and ValidTo columns in temporal tables in EFCore in C#? This is how I initialized temporal table protected override void OnModelCreating(ModelBuilder modelBuilder) { …
Monset
  • 648
  • 5
  • 25
4
votes
0 answers

How to delete history from temporal tables via Entity Framework Core

Temporal tables are awesome and the implementation is simple, so I love them! But, I have a concern regarding deletion of history in temporal tables. I cannot find how to do that. Why would I do that? Because I'm implementing temporal tables in my…
Monset
  • 648
  • 5
  • 25
1
2 3
…
12 13