question

SurendraAdhikari-2570 avatar image
0 Votes"
SurendraAdhikari-2570 asked Criszhan-msft edited

columns filter in system versioned temporal table

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?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

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://docs.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://docs.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.