DATA_CONSISTENCY_CHECK is On in my table.But still temporal table inserts another row for same data update .How can I restrict in T-SQL?

Question

DATA_CONSISTENCY_CHECK is On in my table. I'm trying to check data consistency for audit purpose.When I update same value in the main table, the temporal table keeps history of the same row, which causes difficult to track the version changes.I'm using MSSQL server.

Answer 1

You misunderstood the function of DATA_CONSISTENCY_CHECK option. It's used to check if time ranges definded by system_start_time_column_name and system_end_time_column_name columns in PERIOD FOR SYSTEM_TIME do not overlap in base and historical table when you enable the link between the base and historical table ( this done when you execute CREATE/ALTER TABLE command).

If you need data deduplication in historical table you have to implement it yourself. It can be a maintenance task which disable the link, remove duplicates, update the time range columns correctely and enable link between base and historical table back.