6

I am looking at creating temporal tables https://msdn.microsoft.com/en-us/library/mt604462.aspx in our database but I cant on a couple of tables that have computed columns.

The error message returned is rather self explanatory

"Computed column is defined with a user-defined function which is not allowed with system-versioned table"

but I was hoping there was a way to exclude or ignore columns from being tracked?

I have tried dropping the computed column creating the history table then adding the computed column back into the table but this didn't work.

Any help is appreciated.

Thanks

Edit -
I wasn't able to find a way to ignore columns from being tracked but we were able to refactor out the columns that used UDFs thus enabling us to use temporal tables.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Macilquham
  • 282
  • 1
  • 10

2 Answers2

7

I was struggling with adding a computed column to an existing system-versioned table. In case anyone else with a similar problem lands here, I finally realized that the history table doesn't treat the column the same way. It ends up being similar to having an IDENTITY column on the base table, but that would result in a regular INT field on the history table.

If you are attempting to add a computed column to a system-versioned (temporal) table:

  • First turn off system versioning
  • Then add your computed column to the base table
  • Verify the "type" of the resulting computed column
  • Add the column with the appropriate static type to the history table
  • Turn system versioning back on (DO NOT FORGET TO SPECIFY THE HISTORY TABLE)

I find it rather odd that you can accidentally omit the history_table when turning system versioning back on. I'd expect either it would resume versioning to the same table OR throw some kind of error considering it might be a bit unexpected behavior.

@pasquale-ceglie - I don't have enough reputation to comment, but I wanted to expand on what you said. You should be able to use most computed columns with temporal tables, just more manually. Basically you can't copy the schema definition with the computed columns, you can however replicate the resulting columns and generate the appropriate history table before trying to turn everything on. The definitions are just a bit different between the two tables (was quite confusing to me at first). I subscribed here, ping me if the above isn't clear and are curious.

Brian Jorden
  • 1,166
  • 10
  • 9
-1

System-versioned table schema modification fail because adding computed column while system-versioning is ON is not supported, so for the same reason you can't transform a regular table into a temporal one if there are computed columns on it.

Hope will help,

paSQuaLe ceglie
  • 29
  • 2
  • "you can't transform a regular table into a temporal one if there are computed columns on it" - you can. I just did it. But system versioning must be off when you create or alter that column, then you turn system versioning back on again. – Reversed Engineer Feb 01 '18 at 14:55