2

We are looking at implementing a bitemporal solution for a few of our tables, because they must record both application time and system time.

I know SQL 2016 has native support for only the system-time component, so we will be converting tables to be temporal and modifying what we have now to create a fully functional bitemporal solution.

My question is about consistency. The system-time end date component of the temporal table is set to 9999-12-31 23:59:59.9999999, so I thought it would be a good idea to set our application/valid time end date to also be 9999-12-31 23:59:59.9999999.

However, I have been asked "Why can't we just set it to NULL to indicate that the period in the main table has no end?"

So why is that? Why did MS choose to use 9999-12-31 23:59:59.9999999 rather then NULL?

Is it as simple as making queries (potentially) easier to write? I guess BETWEEN works better with two actual date values, but I can't think of much else.

Crogacht
  • 90
  • 1
  • 6

1 Answers1

2

Its because those columns are Period columns. (In essence you are correct) Since a period is a definition of time, it logically shouldn't be compared against null.This way MSSQL can compare time values against time values and keep track of all updates ( as opposed to comparing against null and having to assume null doents mean lack of data but an end of a period.

Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column. MSDN

Community
  • 1
  • 1
gh9
  • 10,169
  • 10
  • 63
  • 96
  • Thanks. My columns that record application time are not period columns, so theoretically you could use NULL for the EndDate. I guess if MS add support for application time, then we will have something like PERIOD FOR APPLICATION_TIME, which will create the period. Your point about NULL being lack of data and not indicative of the end of a period is a good point and will help me argue the case. – Crogacht Dec 11 '18 at 22:15
  • @Crogacht So you didn't create a temporal table with `system versioning = on`? – gh9 Dec 11 '18 at 22:21
  • No, I did. The system versioned columns are all working as you'd expect, with 9999-12-31 23:59:59.9999999 as the EndDate, but when it comes to my application time columns, it looks like I have the option to use either NULL OR 9999-12-31 23:59:59.9999999 as the EndDate because they are not period columns. However, 9999-12-31 23:59:59.9999999 seems cleaner and more consistent. – Crogacht Dec 11 '18 at 22:24
  • Cool, a `Period Column` is just a name MS uses. It just refeers to the two columns that MS creates on all Temp Tables. They arent really a thing its just a naming convention for the two auto generated columns – gh9 Dec 11 '18 at 22:27
  • when I said Temp tables I was using shorthand for Temporal Tabls – gh9 Dec 12 '18 at 01:47