CODEWITHSUNDEEP
sql-serversql-server-2016temporal-tables
cloudsafe
cloudsafe

Reputation: 2506

Is using a wide temporal table with only one regularly updated column efficient?

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.

Upvotes: 2

Views: 358

Answers (1)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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

Upvotes: 1

Related Questions