SQL Server Temporal tables

Question

I have been asked to track changes of a column, basically I will be getting flat files on a weekly basis that I need to upload then I need to compare each column values from the old file to the new file. I was researched online and came across temporal tables but not sure if it's the right approach. 

In the screenshot above line 1 column delivery model got changed this week (line 2) and I need to track it.

Thanks,

Ali.

Answer 1

Hi @Ali Ahad

Common methods of tracking data include Change tracking (CT), Change Data Capture (CDC) and Temporal Tables.

Temporal table, also known as system version table. It allows SQL Server to automatically maintain and manage the history of data in tables. This feature provides a complete history of every change made to the data.

You can refer to this document to confirm the Temporal table usage scenarios: https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-ver16.

This article provides a simple example: https://www.sqlshack.com/temporal-tables-in-sql-server/.

Hope these can help you.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya

Answer 2

If you want to know the answer to a question like "what was the data for customer X on 7 aug, 14:30", temporal tables are a good fit.

Whether it is a good fit in your case, is not clear to me, because your description is too vague. If you load a file and all your want to do is to compare it to the current contents, then just load the file to a side table with the same structure. It could help if you could clarify what your ultimate aim is.