0

are there any information in the net, where i can verify how hight are the storage costs for temporal tables feature?

Will the server creates a the full hardcopy of the row/tuple that was modified? Or will the server use a reference/links to the original values of the master table that are not modified?

For example. I have a row with 10 columns = storage 100 KB. I change one value of that row, thow times. I have thow rows in the historical table after that changes. Is the fill storage cost for the master und historial table then ~300KB?

Thanks for every hint!

Ragards

user1481065
  • 487
  • 1
  • 4
  • 16
  • It is not a comprehensive storage guide but there is at least one warning about storage [here](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations) ... *While temporal tables support blob data types ... they will incur significant storage costs ... care should be taken when using these data types* – ta.speot.is Feb 09 '18 at 07:34
  • 1
    If someone provides an answer and it's "too costly" for you, what alternatives are you considering? Is there a reason you cannot setup experiments and *measure* these things yourself? – Damien_The_Unbeliever Feb 09 '18 at 07:35
  • i use currently a selft build legacy solution from sql server 2005, where i copy only the value of the changes column and the column name in a dedicated table. I plan to replace this legacy construct with the temporal table feature. – user1481065 Feb 09 '18 at 07:38

1 Answers1

0

Will the server creates a the full hardcopy of the row/tuple that was modified? Or will the server use a reference/links to the original values of the master table that are not modified?

Here is the cite of the book Pro SQL Server Internals by Dmitri Korotkevitch that ansers your question:

In a nutshell, each temporal table consists of two tables — the current table with the current data, and a history table that stores old versions of the rows. Every time you modify or delete data in the current table, SQL Server adds an original version of those rows to the history table.

A current table should always have a primary key defined. Moreover, both current and history tables should have two datetime2 columns, called period columns, that indicate the lifetime of the row. SQL Server populates these columns automatically based on transaction start time when the new versions of the rows were created. When a row has been modified several times in one transaction, SQL Server does not preserve uncommitted intermediary row versions in the history table.

SQL Server places the history tables in a default filegroup, creating non-unique clustered indexes on the two datetime2 columns that control row lifetime. It does not create any other indexes on the table.

In both the Enterprise and Developer Editions, history tables use page compression by default.

So it's not

reference/links to the original values of the master table

Previous row version is just copied as it is into historical table on every mofification.

sepupic
  • 8,409
  • 1
  • 9
  • 20