SQL Server - Temporal Table - Selected Columns Only

Question

One of the requirements of a recent project I was working on, was maintaining history of database table data as part of an audit trail. My first thought about the technical solution was to use triggers, but after some research I learned about SQL Server temporal tables (Part of core SQL Server 2016). I did a lot of research around this and see that Temporal tables can be put to good use.

More on temporal tables: Managing Temporal Table History in SQL Server 2016

However, I want the data in temporal tables to be created only when few columns are changed.

CREATE TABLE dbo.Persons
(
  ID BIGINT IDENTITY(1,1) NOT NULL,
  FirstName NVARCHAR(50) NOT NULL,
  LastName NVARCHAR(50),
  PhoneNumber NVARCHAR(20)
)

Now if I create the temporal table on top of this (SYSTEM_VERSIONING = On), I want the data to be inserted in the Temporal table only when Phone Number is changed and not the first name and last name.

Answer 1

You could create one table for the attributes you want history for (and you'll set system_versioning = ON) and a second table with the attributes you don't want history for. Between the two tables you'll have a 1-to-1 relation.

Answer 2

Unfortunately, that's not the way it works. Like the link in your post says, "system versioning is all-or-nothing". Honestly, your first instinct is likely your best option - every other method of doing it (CDC, replication, system versioning..) will capture more data than you want and you will have to pare the results down after the fact.

If you really want to use system versioning, you'd just have to use one of the options presented in the provided link: delete unwanted rows and/or update unwanted columns to NULL values.

I would recommend going with your first instinct and use triggers to implement something like a type 4 slowly changing dimension. It's the most straightforward method of getting the specific data you want.