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 ability to investigate only last modified columns).

  1. 1st problem is that our R&D are trying to avoid from altering table structure (adding columns) as it is a sensitive and big table.

  2. 2nd problem is that the original table contains MODIF DATE column. This column is being updated at the datetime value of the update transaction beginning time warriors I need the COMMIT time.(gaps are critical)

enter image description here

My question:

Is there a possibility to turn the original table to Temporal one , without adding columns and only by using the CREATION date and MODIF date built-in columns?

Will appreciate any support here!


1 Answer 1


If you're using System-Versioned Temporal Tables then I believe you need not change your table schema. Rather separate tables are created to automatically manage history on your existing tables.

There's also alternatives in SQL Server that shouldn't require you to change your existing table schema either such as Change Tracking or Change Data Capture (CDC).

  • Thanks allot for your quick support. But even in the article you've attached I can see that adding datetime columns on the original table is essential. As for the other alternative's I prefer avoiding from using other solutions now , unless there will be no bypassing way to the table schema changes.
    – Yaniv A
    Feb 21, 2021 at 15:10
  • @user4443389 I'd be rather surprised if that was a hard requirement of Temporal Tables but I haven't looked into them in a while, so it's definitely possible. I'll have to refresh on the documentation I linked because it offers about 5 different ways of using Temporal Tables and I'll update my answer accordingly. If it is a requirement, then I think your only alternatives are the ones I linked. Change Tracking is probably the simplest and minimalist footprint to implement of the two.
    – J.D.
    Feb 21, 2021 at 16:27
  • Thanks , awaiting your updates.
    – Yaniv A
    Feb 22, 2021 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.