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
2answers
52 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 ...
1
vote
0answers
28 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 ...
8
votes
2answers
706 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 ...
0
votes
1answer
62 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
0answers
46 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 ...
0
votes
1answer
41 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 +----+-------+----------...
0
votes
0answers
23 views

Is there a way for the temporal tables to log to history table only when any of the value changes? [duplicate]

I want to Audit/insert the record to history table only when any of the value is modified in temporal table. Thanks!
4
votes
1answer
96 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 ...
1
vote
1answer
187 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 ...
0
votes
1answer
230 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 ...
3
votes
1answer
77 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 ...
6
votes
1answer
118 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 ...
1
vote
1answer
288 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 ...
8
votes
1answer
394 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 ...
1
vote
1answer
58 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 ...
2
votes
2answers
465 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 ...
8
votes
2answers
3k 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
1answer
50 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
0answers
442 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 ...
2
votes
1answer
1k 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 ...
1
vote
1answer
69 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. ...
0
votes
1answer
745 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,...
0
votes
1answer
298 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 ...
4
votes
1answer
227 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
votes
1answer
274 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: ...
6
votes
1answer
3k 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
vote
1answer
120 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.
1
vote
2answers
401 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 ...
7
votes
2answers
5k 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].[...
2
votes
1answer
1k 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, ...
0
votes
0answers
89 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 ...
1
vote
1answer
625 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 ...
4
votes
1answer
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 ...
6
votes
4answers
4k 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: ...
4
votes
2answers
1k views

Where can I get SQL Server Transaction Begin Time from?

I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2 Is there a function or table in SQL Server to get that same transaction begin ...
4
votes
2answers
2k views

Change the behavior of temporal tables to log actual value changes rather than dummy updates

Currently the articles I read on temporal tables push the responsibility of the clean-up to the application code. Is it possible to configure the temporal tables to record particular column value ...
8
votes
1answer
4k views

JOIN FOR SYSTEM TIME with column as the effective system time

Imagine I have a schema that includes Products, Orders, and OrderLineItems, with Products being a system-versioned temporal table. Schema: CREATE TABLE dbo.Products ( ProductID INT NOT NULL ...
5
votes
1answer
841 views

Should 'FOR system_time ALL' include rows where the start and end times are equal?

Querying a system-versioned temporal table with FOR system_time ALL appears to omit rows where the start and end times are equal. For example: /* Create a simple system-versioned table */ CREATE ...
2
votes
0answers
2k views

Something similar to GENERATED ALWAYS for a DEFAULT? (SQL Server / Azure SQL)

Question: Is there any way to force a column to always use the default value definition? I would like to always have the user_id populated on an insert/update (I know delete might be a bit different) ...
3
votes
0answers
78 views

Workout change operation with temporal table records

I have just started using using temporal tables/system versioning, its very easy to setup and get going in terms of querying the data. The one thing missing for me is storing the change operation (...
2
votes
1answer
270 views

Stack dumps when turning on Temporal Table SYSTEM_VERSIONING

I am running SQL Server 2016 SP1 (13.0.4001) on Windows Sever 2012 R2. I have the below table. CREATE TABLE [SubEx].[Ingredients]( [IngredientId] [int] IDENTITY(1,2) NOT NULL, [IngredientName] ...
5
votes
2answers
1k views

SQL Server 2016 Temporal Tables

Has anyone tried prepopulating temporal tables with old history data? Before we decide to use temporal tables we need to be able to import our old history into them.
1
vote
1answer
80 views

Using SQL Server's 2016 Temporal Tables to Record Prior History

I've been reading with interest about SQL Server 2016's Temporal Table capability. It almost seems like the answer to a problem I have (and I'm sure many have had), except for one thing: StartTime ...
1
vote
0answers
277 views

Definition and declaration of a PRIMARY KEY for a temporal table

My teacher gave us an example bitemporal table holding rows related to medical records to test our homework on. The table has the columns that follow: first name last name parameter value unit valid ...
7
votes
1answer
6k views

SQL Server 2016: Temporal table performance vs. Triggers and CDC

It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended ...
0
votes
0answers
1k views

Deadlocks on primary key row locks race condition with temporal tables

I have a procedure dbo.DeleteTemporal which begins a transaction and then deletes records from a list of 100 or so tables all based on a column named ServerName. The list of tables and the order of ...
9
votes
3answers
2k views

Why do temporal tables log the begin time of the transaction?

When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the ...
8
votes
1answer
894 views

Do temporal tables log changes when there are none?

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema: create table blah ( foo int identity(1,1), baa int ) and I ...
0
votes
1answer
86 views

Combine various temporal criteria to filter rows

I am trying to query a database table with some filters. I have a form through which a user will create query attributes: When user will click on search it will pass such values: {camera_id: "9879" ...
4
votes
2answers
378 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...