Variant Data Type to store bitemporal columns

Question

We have a requirement for bitemporal data. In order to manage the datasets we are adding four columns:

• value_start_date
• value_end_date
• system_start_date
• system_end_date.

There was a suggestion to consider a variant datatype for these four columns and store in JSON format. I am not convinced there is any value add for going for variant datatype. The schema is fixed and there is a need to look at the start and end dates on a frequent basis. Also bitemporal involves updating the prior records. Anyone else have another perspective on this?

Answer 1

JSON is a great format for storing semi-structured data.

But if you know the exact type, usage, and column names - choose the traditionally structured columns for storage.

You haven't given any argument in favor of using a variant type, so it's hard to empathize with the other point of view anyways.