2

I have been unable to pin down how temporal table histories are stored. If you have a table with several columns of nvarchar data and one stock quantity column that is updated regularly, does SQL Server store copies of the static columns for each change made to stock quantity, or is there an object-oriented method of storing the data? I want to include all columns in the history because it is possible there will be rare changes to the nvarchar columns, but wary of the table history size if millions of qty updates are duplicating the other columns.

cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • I don't think SQL Server, or any other SQL database, will duplicate data in the background, not without you configuring something to do so. – Tim Biegeleisen Apr 16 '18 at 11:17
  • Check [this question](https://stackoverflow.com/questions/48700772/sql-server-temporal-table-storage-costs) – Vojtěch Dohnal Apr 16 '18 at 11:27
  • I've never used them but I suggest you experiment and the answer will become clear. MSDN says _a temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted_ so it would appear the full table is snapshotted each time – Nick.Mc Apr 16 '18 at 11:34
  • @Nick.McDermaid I have thought to compare but there are other differences between regular and temporal tables including compression, so it is not easy to tell. – cloudsafe Apr 16 '18 at 11:46

1 Answers1

1

I suggest that you use the SQL Server temporal table only for the values that need monitoring otherwise the fixed unchanging attribute values would get duplicated with every change. SQL Server stores a whole new row whenever a row update occurs. See the docs:

UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock

You need to move your fixed varchar attributes/fields to another table and use a relation, 1:1 or whatever will be suitable.

Check also other relevant questions under the temporal-tables tag:

SQL Server - Temporal Table - Storage costs

SQL Server Temporal Table Creating Duplicate Records

Duplicates in temporal history table

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
  • @Vojtech_Dohnal Your ink 'SQL Server - Temporal Table - Storage costs' certainly suggests that this data is duplicated throughout the history, which is disappointing. Thanks. – cloudsafe Apr 16 '18 at 12:19
  • 1
    If it's disappointing, vertically partition your table into columns that change frequently and those that don't. You have options. – Ben Thul Apr 16 '18 at 13:02