columns filter in system versioned temporal table

Question

Can we log changes only in certain columns, not in all, in the history table of system versioned temporal table? In a table there are some columns for which data changes more frequently which I dont need to log because logging those changes increased data size enormously. Or can we remove the log of those changes? I only need to log changes in other columns for which data changes less frequently. Is there a way to achieve this?

Answer 1

Hi,

The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.
https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15

I tried to create a temporal table with a user-defined history table, and made a column name of the history table inconsistent with the temporal table. But I got following error.

Msg 13524, Level 16, State 1, Line 18
Setting SYSTEM_VERSIONING to ON failed because column 'SysEndTime2' at ordinal 6 in history table 'test02.dbo.DepartmentHistory' has a different name than the column 'SysEndTime' at the same ordinal in table 'test02.dbo.Department'.

Manage retention of historical data in system-versioned temporal tables
https://learn.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables?view=sql-server-ver15