How to exclude some column from history table in temporal table

191 Views Asked by At

I have temporal table like below

-- Create a Temporal Table
CREATE TABLE dbo.Sample
(
  SampleId int identity(1,1) PRIMARY KEY CLUSTERED
  , SampleDate date NOT NULL
  , Data1 varchar(50)
  , Data2 varchar(50)
  -- Other fields...
  , SysStartTime datetime2 GENERATED ALWAYS AS ROW START
  , SysEndTime datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SampleHistory));

I want to say all of changes in Data2 does not store in HISTORY_TABLE. In another word, in very large and big tables I just need few columns for auditing so I want to exclude other columns to reduce DB size and improve performance (in some cases most frequent changes in table occur on non-auditable column)

How to do it?

0

There are 0 best solutions below