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
1
vote
1answer
26 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
27 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 ...
0
votes
0answers
11 views

Error 193 while installing temporal_tables

I am trying to install temporal tables for my postgresql 9.5.1, i am using windows 7 64bit i followed correctly the steps of installation but when i run this commande : > CREATE EXTENSION ...
3
votes
1answer
59 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
74 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
64 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 ...
0
votes
0answers
35 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
234 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
35 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
225 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
1k 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
35 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
201 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
656 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
52 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
411 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
175 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
156 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
130 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: ...
4
votes
1answer
1k 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
72 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
267 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
1answer
2k 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
710 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
75 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
395 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
647 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
2k 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: ...
3
votes
2answers
750 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
1k 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 ...
7
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 ...
5
votes
1answer
547 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
1k 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
68 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
227 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
74 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
233 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
5k 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
865 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 ...
8
votes
3answers
973 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 ...
7
votes
1answer
597 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
79 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
337 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
147 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
309 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
71 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 | | | +-+--+-...