Sql-server – Creating Temporal table without adding DateTime columns

sql servertemporal-tables

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).

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.
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)

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!

Best Answer

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).

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO

Sql-server – Is it against normal form to include columns with date parts if there is a datetime column in the same table

It is against either 2nd or 3rd normal form, depending on whether the datetime column is part of the key.

You could be harmed by an update anomaly if you change a batch date, but forget to change one of the fields that are derived from it. Now the database is self cntradictory.

When I was faced with this, I adopted an alternative approach. I created a table called almanac, with a date type key, and all those other fields like weekday, fiscal quarter, and so on. I then wrote a program to fill this table with ten years worth of dates. Then, whenever I wanted all those fields with respect to any date, I just did a join. This was especially useful because fiscal quarter was a company defined attribute, and wasn't in any built in function. We had dozens of date fields, and reporting by fiscal quarter was a requirement.

Best Answer

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

Sql-server – Is it against normal form to include columns with date parts if there is a datetime column in the same table

Related Question