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
0 votes
1 answer
31 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 ...
user avatar
  • 1,258
1 vote
1 answer
43 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 ...
user avatar
5 votes
1 answer
212 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:...
user avatar
2 votes
1 answer
85 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 ...
user avatar
  • 1,222
0 votes
0 answers
158 views

Migrating MS SQL Server Temporal Tables to AWS Aurora PostgreSQL

I'm hoping you can lend some pointers on this problem. We are soon going to be undertaking a migration from AWS RDS MS SQL Server to AWS Aurora PostgreSQL mainly for cost savings and the ability to ...
user avatar
0 votes
0 answers
49 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 ...
user avatar
  • 1,735
0 votes
2 answers
79 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, ...
user avatar
  • 400
1 vote
1 answer
79 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 ...
user avatar
1 vote
1 answer
76 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 ...
user avatar
  • 519
1 vote
1 answer
67 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 ...
user avatar
  • 21.1k
2 votes
1 answer
246 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 ...
user avatar
2 votes
1 answer
125 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 ...
user avatar
  • 121
2 votes
1 answer
109 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 ...
user avatar
  • 155
0 votes
1 answer
998 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, ...
user avatar
  • 285
0 votes
1 answer
53 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 ...
user avatar
0 votes
1 answer
42 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 ...
user avatar
  • 191
2 votes
1 answer
88 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]...
user avatar
  • 23
0 votes
2 answers
563 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 ...
user avatar
  • 73
1 vote
0 answers
75 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 ...
user avatar
8 votes
2 answers
1k 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 ...
user avatar
  • 191
0 votes
1 answer
428 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" ...
user avatar
0 votes
0 answers
97 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 ...
user avatar
0 votes
1 answer
163 views

Aggragate 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 +----+-------+----------...
user avatar
4 votes
1 answer
278 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 ...
user avatar
2 votes
1 answer
489 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 ...
user avatar
  • 2,394
0 votes
1 answer
867 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 ...
user avatar
  • 103
3 votes
1 answer
92 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 ...
user avatar
6 votes
1 answer
156 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 ...
user avatar
1 vote
1 answer
533 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 ...
user avatar
8 votes
1 answer
564 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 ...
user avatar
  • 83
1 vote
1 answer
113 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 ...
user avatar
  • 113
2 votes
2 answers
624 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 ...
user avatar
  • 123
11 votes
2 answers
5k 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 ...
user avatar
0 votes
1 answer
63 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 ...
user avatar
4 votes
0 answers
621 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 ...
user avatar
5 votes
1 answer
2k 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
85 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
1k 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
353 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 ...
user avatar
  • 911
4 votes
1 answer
281 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 ...
user avatar
  • 3,609
3 votes
1 answer
414 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: ...
user avatar
8 votes
1 answer
6k 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 ...
user avatar
  • 1,318
1 vote
1 answer
224 views

Always encrypted on Temporal tables

Is it now possible to apply Always encrypted to some columns on existing Azure SQL database temporal tables with existing data ? I know it was not possible a few months ago thru SSMS / powershell.
user avatar
  • 11
1 vote
2 answers
506 views

SQL Server 2016 Temporal tables metadata

Is there any way to get metadata (information scheme) information about temporal table feature for the table, like: Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF What name of history table Is ...
user avatar
9 votes
4 answers
9k views

ADD PERIOD FOR SYSTEM_TIME on table failed

I have: table with existing data SQL Server 2016 SP1 SQL Server Management Studio 17.5 I am using the following statement to make a make my table a temporal one: ALTER TABLE [dbo].[...
user avatar
  • 3,609
2 votes
1 answer
2k views

Does anyone have a stored procedure to truncate temporal table?

Does anyone have or know of stored procedure that will do generic temporal table manipulation so that history and main table are both truncated? You have to alter it to set system_versioning = off, ...
user avatar
  • 301
0 votes
0 answers
122 views

Can you do Log Shipping from Production non-Temporal to Data Lake Temporal setup Table?

Can you do log-shipping from a table in production without temporal tables to a secondary that has temporal tables setup? Details: We want a frozen nightly / yesterdays-data-only data-lake. And we ...
user avatar
1 vote
1 answer
885 views

What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day?

I have a SQL Server 2016 temporal table query that I’m having a hard time figuring out an efficient way to write. Let’s say I have a temporal table called [Users] that each day has the total body of ...
user avatar
4 votes
1 answer
1k views

SQL Server 2016: Adding temporal tables to existing application

I have a big PHP application, developed by an external company, which is being completed right now; I cannot ask for changes to it. It uses a database Microsoft SQL Server 2016, which is accessed by ...
user avatar
  • 1,407
10 votes
4 answers
7k views

Error 13542 enabling system-versioning for existing tables

I'm trying to enable system-versioning for some existing tables in a SQL Server 2016 database, which contain data. I'm following these instructions from Microsoft. One of the tables looks like this: ...
user avatar