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.
75
questions
3
votes
2
answers
87
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.) ...
- 151
0
votes
1
answer
49
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 ...
- 3
0
votes
1
answer
31
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? (...
- 235
0
votes
1
answer
19
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 ...
- 103
0
votes
1
answer
86
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 ...
0
votes
1
answer
440
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 ...
- 1,280
1
vote
1
answer
75
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 ...
- 53
5
votes
1
answer
572
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:...
- 153
2
votes
1
answer
368
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 ...
- 1,264
0
votes
0
answers
121
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 ...
- 1,783
0
votes
2
answers
533
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,
...
- 496
1
vote
1
answer
170
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 ...
1
vote
1
answer
186
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 ...
- 519
1
vote
1
answer
153
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 ...
- 22.6k
2
votes
1
answer
501
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 ...
- 349
2
votes
1
answer
244
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 ...
- 121
2
votes
1
answer
165
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 ...
- 156
0
votes
1
answer
2k
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, ...
- 285
0
votes
1
answer
110
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 ...
- 1
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 ...
- 191
2
votes
1
answer
164
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]...
- 123
0
votes
2
answers
1k
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 ...
- 83
1
vote
0
answers
99
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 ...
- 11
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 ...
- 191
0
votes
1
answer
709
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" ...
0
votes
0
answers
113
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 ...
- 173
0
votes
1
answer
266
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
+----+-------+----------...
- 101
4
votes
1
answer
532
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 ...
- 143
2
votes
1
answer
707
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 ...
- 2,442
0
votes
1
answer
1k
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 ...
- 103
3
votes
1
answer
104
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 ...
- 169
6
votes
1
answer
196
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 ...
- 9,202
1
vote
1
answer
665
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 ...
- 121
8
votes
1
answer
686
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 ...
- 83
1
vote
1
answer
186
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 ...
- 113
2
votes
2
answers
735
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 ...
- 123
12
votes
2
answers
6k
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 ...
0
votes
1
answer
77
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 ...
4
votes
0
answers
719
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 ...
- 41
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 ...
user162241
1
vote
1
answer
100
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. ...
user162241
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,...
user162241
0
votes
1
answer
395
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 ...
- 911
4
votes
1
answer
308
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 ...
- 3,721
3
votes
1
answer
476
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: ...
- 33
12
votes
1
answer
8k
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 ...
- 1,378
1
vote
1
answer
301
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.
- 11
1
vote
2
answers
571
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 ...
9
votes
4
answers
11k
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].[...
- 3,721
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, ...
- 301