CODEWITHSUNDEEP
sql-server
user9393635
user9393635

Reputation: 1429

Trying to validate if a SQL Server data model uses temporal tables?

Looks like SQL Server 2008 and later uses the concept of "temporal tables" to manage table data history:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios

Looks like the following clause is used to accomplish this:

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTableHistory));

Let's assume that a data model has tables TableX and a TableXHistory and I select the following context menu path to generate a DDL script of TableX:

Script Table as > CREATE to > New Query Editor Window

If the generated SQL script does not have a text reference to "HISTORY_TABLE" then can I say 100% that the history table is not managed as a temporal table? Also, would a temporal table be explicitly displayed in the standard tables directory for the data model? Is there any reason not to use temporal tables in 2018 as opposed to manually created history tables? My first impression is that anyone who creates manual history tables in 2018 is most likely out of date with SQL Server capabilities.

Upvotes: 1

Views: 358

Answers (1)

Anton
Anton

Reputation: 2882

Temporal tables available only from 2016. Technology is not mature yet.

Temporal tables have their own Pros & Cons. Other options should be considered (classic triggers and history table, change data capture, replication, etc.)

The main disadvantages of temporal tables for me:

  • multiple changes made at the same time are invisible (only one row is returned)
  • history tables must be located at the same DB
  • limitation for transactional replication, merge replication is not supported
  • issues when system time has been changed - no way to know which update was first w/o implementing additional logic (version)
  • history tables can'be updated w/o disabling versioning
  • to get net changes you need to query the base table (which is not good).
  • how to detect which columns are changed? (CDC & triggers can detect that naturally, with temporal it may be very expensive) ...

Upvotes: 4

Related Questions