0

I have this database table Templates and I need to keep historical information. So in code what I am doing is inserting a new row with the updated data and then setting the IsCurrentVersion flag to false. This is working fine but I am not sure how this will grow. Will that have performance problems? Is there a better way to do this? would a archive table be better?

Table Templates {
  id int [pk]
  Title varchar
  Added datetime
  Moddified datatime
  Deleted datetime
  IsCurrentVersion bit
}

enter image description here

Improve this question
1

2 Answers 2

Reset to default
1

Instead of a bit for IsCurrentVersion, I'd suggest setting up a version table and then use queries that let you pick the max version. I have several examples of the necessary code in this article. This method performs extremely well and has scaled well over time. However, there are alternatives that also work well. Between these two methods, you should be able to do exactly what you're looking for.

Improve this answer
2
  • on the pre-calculated column IsLastVersion why are the modifications slower? I understand why it is fast.
    – Jefferson
    Oct 9, 2020 at 13:59
  • Testing will always be your friend here. I would expect, depending on how you set up the queries, that the IsCurrentVersion will yield poor row counts in the stats, leading to scans for the data.
    – Grant Fritchey
    Oct 12, 2020 at 11:31
0

SQL Server 2016 and later have a feature called Temporal Tables. These will keep change history and give you tools to query historical values at any point in time.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.