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.
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