Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
13 views

Postgresql: Range in a primary key

I want to create a temporal table describing time behaviour of entities as described in Snodgrass's book. My question is if the time range should be included or not in the primary key. For example, ...
8
votes
1answer
195 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 ...
0
votes
1answer
19 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
55 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 ...
6
votes
2answers
243 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
29 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 ...
3
votes
0answers
59 views

Error on rename for a column on a temporal table

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 ...
1
vote
1answer
151 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
40 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
162 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
118 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
98 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 ...
2
votes
1answer
76 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: ...
2
votes
1answer
379 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
51 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.
0
votes
0answers
83 views

Should FOR SYSTEM_TIME AS OF @Date be used on queries for current data or should I split the stored procedure logic?

Long time listener, first time caller. Some time last year we made the move to Azure and have been using temporal tables to populate the data for our archive feature in the application instead of ...
1
vote
2answers
169 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 ...
5
votes
1answer
1k 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].[...
1
vote
1answer
431 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
71 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
279 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
452 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 ...
5
votes
3answers
1k 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
1answer
560 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
1answer
825 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 ...
5
votes
1answer
2k 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 ...
4
votes
1answer
350 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
906 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
62 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 (...
3
votes
1answer
206 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]...
6
votes
2answers
811 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
69 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
217 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 ...
6
votes
1answer
4k 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
780 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 ...
7
votes
3answers
780 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 ...
6
votes
1answer
453 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
77 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
301 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 ...
5
votes
1answer
2k views

Will Temporal Tables be included in the Standard Edition of SQL Server 2016?

The new version of SQL Server 2016 wil support so called Temporal Tables. I can't find any documentation that would say whether this feature would be Enterprise-only or available in Standard edition ...
1
vote
1answer
125 views

Interpreting Decision Time (DT) in temporal databases

Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the ...
17
votes
1answer
1k views

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a ...
1
vote
1answer
1k views

SQL Server: Time Series Table Design

I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps. The difference with my design is that the FinishedAt column changes every time ...
0
votes
1answer
226 views

Records in a table unique period

I have the following table: +--+---------+-------+--------------+--------------+ |ID|HEADER_ID|VERSION|FROM_DATE_TIME| TO_DATE_TIME| +--+---------+-------+--------------+--------------+ | 1| ...
1
vote
1answer
70 views

database design for optional association and temporal

I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities. +-------+ | Task | | | +-+--+-...