Last month I was fortunate to have my first ever article published on Simple-Talk, among the best quality website for sql server articles ever. During the process of writing this article I ran into several errors related to temporal tables that I have not seen before. Some of these are documented by Microsoft, some are fairly obvious to understand and others are not. Below I summarize the list of errors you can possibly run into if you are using this really cool feature.
|ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column.||Defaults have to be specified if using an existing table as a history table.|
|Msg 13523, Level 16, State 1, Line 62 Setting SYSTEM_VERSIONING to ON failed because table ” has ‘’ columns and table ‘WideWorldImporters.Sales.Region_History’ has ‘’ columns.||History table and main table should have exactly the same structure.|
|Msg 13518, Level 16, State 1, Line 62 Setting SYSTEM_VERSIONING to ON failed because history table ‘’ has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.||Columns with identity are disallowed on history table.|
|Msg 13575, Level 16, State 0, Line 2 ADD PERIOD FOR SYSTEM_TIME failed because table ” contains records where end of period is not equal to MAX datetime.||End date on table being versioned has a value that is not equal to the default specified. Update the table to make sure all end dates equal the maximum default.|
|Msg 13515, Level 16, State 1, Line 15 Setting SYSTEM_VERSIONING to ON failed because history table ‘’ has custom unique keys defined. Consider dropping all unique keys and trying again.||History table cannot have any primary keys defined. Remove primary keys.|
|Msg 13543, Level 16, State 0, Line 116 Setting SYSTEM_VERSIONING to ON failed because history table ” contains invalid records with end of period set to a value in the future.||Data in history table is not following rules – all end date should be before start date for the corresponding main record, and less than current date.|
|Msg 13573, Level 16, State 0, Line 7 Setting SYSTEM_VERSIONING to ON failed because history table ” contains overlapping records.||There are multiple records for the same record with overlapping start and end dates. The end date for the last row in the history table should match the start date for the active record in the parent table|