Questions tagged [temporal-tables]

Use this tag when referencing the temporal table feature introduced in Microsoft SQL Server 2016. Temporal tables add built-in support for providing information about the data stored in a user table at any point in time, rather than just the current state.

Filter by
Sorted by
Tagged with
-3 votes
1 answer
163 views

How to exclude some column from history table in temporal table

I have a same question in Stack Overflow about this, But I hope to find answer here I have temporal table like below -- Create a Temporal Table CREATE TABLE dbo.Sample ( SampleId int identity(1,1) ...
sorosh_sabz's user avatar
1 vote
0 answers
35 views

How to Preserve Past Order Details Amidst Customer Information Changes?

I'm facing a scenario in my e-commerce setup where after an order is marked as Done, any subsequent changes made by the customer to their information (like shipping address) are reflected incorrectly ...
Ashwin Jacob's user avatar
5 votes
1 answer
447 views

Can I enable system versioning (temporal tables) on a large table without scanning the table?

Microsoft docs currently give an example of how to enable temporal tables on existing tables at ALTER TABLE, System Versioning examples: A. Add system versioning to existing tables Using the syntax ...
Michael J Swart's user avatar
0 votes
1 answer
52 views

Maintain changes in database via generic approach triggers into history table

Our main system database has temporal tables These have helped us hugely in working out issues However. A drawback of this is that an issue with doing a lot of updates at once can lead to a an error ...
PaulInUk2017's user avatar
3 votes
2 answers
614 views

In SQL Server Temporal Tables start/end date timestamps are identical for rows that are created inside a EF Core transaction

I'm using a EF Core DbContext transaction to create/update entities on a SQL Server temporal table. The data I'm processing (array of items) has a key (Year-Sequential, like: 2023-001, 2023-002, etc.) ...
Cheshire Cat's user avatar
0 votes
1 answer
250 views

How to check if a table is system versioned for MariaDB?

How to check if a table is system versioned for MariaDB ? This post refers to same question on "SQL Server 2016" How to check if the SYSTEM_VERSIONING for a table is ON? I've tried this ...
John D.'s user avatar
0 votes
1 answer
32 views

How to model/query this data problem? Is this a timeseries issue?

We have a large amount of data in a MS-SQL2014 (v12) server. We need to determine if a product was for sale in some time period. For example, was this inventory for sale in the month of July 2022? (...
Pure.Krome's user avatar
0 votes
1 answer
29 views

how to add a query to postgres and have it show up as a table [closed]

I'm not sure what technology I need because I'm not a DBA, but I'm sure there's a good way to do this. I have a postgres database with this schema (top two tables exist): So I'm trying to add the 3rd ...
MetaStack's user avatar
  • 103
0 votes
1 answer
134 views

Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?

I am going to run maintenance procedure similar to described in Cannot delete rows from a temporal history table . The stored procedure will SET SYSTEM_VERSIONING = OFF , delete 3 million rows on ...
Michael Freidgeim's user avatar
1 vote
1 answer
987 views

How to query the first row of a system-versioned temporal table to get create timestamp and details

I have a database where most of the tables have System-Versioned Temporal Tables (history tables). Often I want to query rows from the 'normal' table and get some data from the oldest record in the ...
Rory's user avatar
  • 1,300
1 vote
1 answer
99 views

What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?

I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process ...
XOR cyst's user avatar
5 votes
2 answers
873 views

Temporal Table: Multiple inserts on each update?

Actual Question: Is there anything in sql 2019 server (besides a trigger) that can cause multiple rows to be inserted in the history table when a single row is updated in the temporal table? Backgound:...
Paul Perrick's user avatar
2 votes
1 answer
693 views

Retention not working on temporal table or very slow?

I have a table with retention set to 25 months (from today is 2019-12-19) but when I query the validfrom/validTo dates directly in the history table, I see records with ValidTo dates before that. We ...
Gabe's user avatar
  • 1,334
0 votes
0 answers
165 views

db2 How to get the application user from delete events

We use temporal tables to store the history of all changes to specific tables in the db. But we also need the user who made the changes. While on db2 side the user is the application, the application ...
Kahn's user avatar
  • 1,813
0 votes
2 answers
1k views

How can I suppress or disable temporal table history insertion for a single statement or transaction?

I have a table that stores Customer information (your bog-standard CRM stuff): CREATE TABLE dbo.Customers ( TenantId int NOT NULL, CustomerId int NOT NULL, ...
Dai's user avatar
  • 574
2 votes
1 answer
289 views

Alternatives to AzureSQL temporal tables

I have recently been learning about temporal tables in AzureSQL and am considering implementing this into my codebase. However, columnstore indexes are only available for Azure SQL databases of tier ...
classicSchmosby98's user avatar
1 vote
1 answer
344 views

Automatically expiring records in a temporal table?

I have a need to store some results that are only valid for a rolling 5 day window. An application will insert or update records and the results should be viewable for the 5 day window. However, there ...
Michael B's user avatar
  • 519
1 vote
1 answer
259 views

Unable to update SQL Server via Access, Reserved error (-7776)

I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the ...
Lennart - Slava Ukraini's user avatar
2 votes
1 answer
893 views

SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a ...
user1930901's user avatar
2 votes
1 answer
390 views

MS SQL temporal tables not properly

I have an existing table and wanted to create temporal tables. I used following queries, it worked in local and dev server. But in QA and PROD the same query created some anonymous temporal table not ...
Akshay's user avatar
  • 121
2 votes
1 answer
300 views

Schema binding seems to not be enforced for view doing temporal query

I am using Microsoft SQL Server 2019. There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related. The ...
Anssssss's user avatar
  • 198
0 votes
1 answer
3k views

Add a column at a specific position in system versioned temporal table

I need to add a column to a temporal table. The Microsoft documentation only suggests appending the new column using the following code: ALTER TABLE dbo.Department ADD TempColumn INT; However, ...
Fletch's user avatar
  • 305
0 votes
1 answer
175 views

Creating Temporal table without adding DateTime columns

I'm an SQL server DBA and new to Temporal tables. We have the following issue: As part of implementing temporal tables mechanism , there's a need to add to datetime columns (so DWH will have the ...
Yaniv A's user avatar
0 votes
1 answer
49 views

Get All data from one table according to another, using Temporal Tables

I have two simple tables: CREATE TABLE [dbo].[StatusForStudents]( [ID] [int] NOT NULL, [PrimaryStatusID] [int] NULL, PRIMARY KEY (ID) ); CREATE TABLE [dbo].[Students]( [ID] [int] NOT ...
Oren A's user avatar
  • 191
2 votes
1 answer
301 views

Temporal table syntax in a view

Consider the following situation. I have a table "test" which is system versioned. I also have a table containing the last time I checked for modified rows in that table. CREATE TABLE [Delta]...
zu1b's user avatar
  • 121
0 votes
2 answers
2k views

last modified rows timestamp for SQL Server 2016

Is there a feature/script to check when was the last time a row has been modified/changed in SQL 2016+ tables?Does SQL track this automatically or do we have a feature which gives this info? Please ...
kevin's user avatar
  • 83
1 vote
0 answers
119 views

SQL Server 2016, history temporal table not compressed by default in Standard Edition

I have a BI database running on SQL Server 2016 Standard SP2 CU14 (13.0.5830.85) in which there are multiple temporal tables defined with default history tables. According to Microsoft, such history ...
Jacek Placek's user avatar
8 votes
2 answers
2k views

Are SQL Server's temporal tables here to stay?

We're considering different approaches to save records' history in an application. Some of the managers in charge are concerned that as this is a relatively new feature of SQL server, and Microsoft ...
Oren A's user avatar
  • 191
0 votes
1 answer
1k views

How do I override the dates with Temporal tables? [duplicate]

I'm trying to use temporal tables for our data lake environment to track changing data over time. The first hurdle I hit is loading historical data to seed the tables. Everything is loading as "now" ...
Mike Nicewarner's user avatar
0 votes
0 answers
138 views

Can Temporal Tables be implemented in SQL 2008 in a way that can be migrated to SQL 2016?

We have a system that is currently used by customers that have versions of SQL from 2008 R2 all the way to 2019. We want to start introducing, in a limited way, Temporal Tables, even for customers ...
Daniel Bragg's user avatar
1 vote
1 answer
488 views

Aggregate values over time in temporal table

Recently we started using temporal tables feature in MSSQL. Now our analytics department wants to write query aggregating data on per month basis. Let's say we have table +----+-------+----------...
Paweł Hemperek's user avatar
4 votes
1 answer
793 views

SQL Server System Versioned Table Execution Timing

I am trying to learn more about system versioned tables in SQL Server so I am running a script to perform simple DML statements on a system versioned table and capturing different times stamps that ...
gomory-chvatal's user avatar
2 votes
1 answer
1k views

SQL Server Temporal Tables History Table size

I have a question regarding the under the hood parts of the History Table in the Temporal Tables feature of Microsoft's SQL Server. To ask it I am going to create a hypothetical situation. Say I ...
Vaccano's user avatar
  • 2,496
0 votes
1 answer
2k views

Load data from a temporal table to another temporal table in Azure

Currently we have only a Live Database where all our data resides. I have created a separate Azure server and DB for development. The DEV DB should be some what up to date with the Live DB for ...
Sindu_'s user avatar
  • 103
3 votes
1 answer
114 views

SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ...
cmcapellan's user avatar
6 votes
1 answer
258 views

Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables? I suspect not, but I ...
John Eisbrener's user avatar
1 vote
1 answer
806 views

Create a partition for a temporal table based on a period

To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension. For my Employee dimension, I created a temporal table. Now I want to split data into ...
Amira Bedhiafi's user avatar
8 votes
1 answer
907 views

Why do I have multiple (unassociated) temporal history tables?

I have been setting up a proof of concept system which has a SQL Server 2017 back end. The system uses temporal tables to record asset configurations and track changes over time. I have a Data table ...
MrB's user avatar
  • 83
1 vote
1 answer
290 views

Time oriented design in Postgresql

I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design ...
madtyn's user avatar
  • 113
2 votes
2 answers
961 views

SQL Server Replication with Historical Tables

Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables? Details: I have a production system, running SQL Server 2014. I want to have a replica ...
power's user avatar
  • 123
13 votes
2 answers
7k views

Poor temporal table performance on older values

I'm encountering a strange issue occurring when accessing historical records within a temporal table. Queries that access the older entries in the temporal table via the AS OF sub-clause take longer ...
Ebrahim Behbahani's user avatar
0 votes
1 answer
90 views

IBM DB2 Timetravel logging based on some criteria

I have been searching for the condition, where, lets say when we enable time travel to a certain table in DB2 , but don't want to capture all the updates done, but only the updates that's done by ...
Yogesh Chavan's user avatar
4 votes
0 answers
819 views

Error on rename for a column on a temporal table [closed]

I've come across an issue when renaming a column in a temporal table. I'm using entity framework to generate migrations, so most of the original SQL is generated, but that doesn't affect my issue. To ...
Gill Harber's user avatar
5 votes
1 answer
3k views

Temporal Table that doesn't use system time

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For the BeginDate, we want it to be dependent on ...
user avatar
1 vote
1 answer
144 views

SQL Temporal Tables Include Current State

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables. We noticed that history tables only include history. ...
user avatar
0 votes
1 answer
2k views

SQL Temporal Tables Begin and End Date

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For our BeginDate and EndDate, we want them to be date,...
user avatar
0 votes
1 answer
469 views

Temporal tables: history table population

According to the prominent diagram here, the main table in a temporal table set-up is populated during the insert, and the history table is populated during update or delete. However when I look at a ...
ajeh's user avatar
  • 911
4 votes
1 answer
336 views

Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?

Using temporal tables one is able to use the FOR_SYSTEM TIME clause with combinations with other options to get valid rows for specific time intervals. For example, we have: FOR SYSTEM_TIME ALL FOR ...
gotqn's user avatar
  • 4,016
3 votes
1 answer
564 views

SQL Server 2016, temporal tables and compressed indexes

We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only). It has come up with 33 issues, all of which are "Abnormal Psychology: ...
Lewis Veale's user avatar
14 votes
2 answers
12k views

Error updating temporal tables

I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming ...
Danilo Braga's user avatar
  • 1,428