0

I have implemented a system versioned (temporal) table on one of our production tables that is frequently updated. This table is updated by applications, power users (manually with custom scripts and standardized sprocs), and system jobs.

I have just run into a situation where I see a new record inserted into the history table but it appears exactly the same as the one before it. As I research this I see it is not a one off situation; there are many more. Please keep in mind the begin and end dates are not duplicated. The end date of the previous record matches the begin date of the next record, with all other data being exactly the same.

I would like to prevent this from happening if possible but first I need to know where this is coming from.

Does anyone have experience with this happening? Does anyone understand temporal tables enough to give me some potential leads on what could trigger an insert into the history table without a visible change to the data? Is it possible to change a record without changing the fields visible to the user?

Thanks in advance.

Adam Osler
  • 1
  • 1
  • I'm pretty sure an update of the table that doesn't actually change data but still sets it (ie, update table set field1 = field1) will cause a new entry in the history table. If you can get in a dev environment, give it a test – Twelfth Nov 02 '17 at 20:59
  • There ya go! That did do it. Makes sense too. Thank you for the help! – Adam Osler Nov 02 '17 at 21:03
  • Turned the comment into an answer for others to refer to in the future. – Twelfth Nov 02 '17 at 21:04
  • @Adam - Can you confirm Twelfth finding and I have same issue and it seems update statement with same value (set field1=field1) – Abdul Azeez Jan 18 '18 at 00:11

1 Answers1

1

I'm pretty sure an update of the table that doesn't actually change data but still sets it (ie, update table set field1 = field1) will cause a new entry in the history table.

I'm going to guess it's being caused by 'power users' scripts (tends to be the number 1 source of screwy code from my history), though the application may also be responsible. Might be in the range of 'code audit' time to find out where the update is coming from.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • 1
    Exactly this. You have to ensure any updates occurring are actually changing values, if not your history table is going to be gigantic and filled with useless entries. See https://bornsql.ca/blog/be-careful-with-table-updates/ – Godwin May 15 '22 at 02:40