SQL Server 2017: Issues with Temporal Tables Not Retaining Data as Expected
I keep running into I can't seem to get I'm reviewing some code and I'm facing an issue with temporal tables in SQL Server 2017 where the historical data does not seem to retain the expected records..... I have set up a temporal table to track changes for my `Employees` table. The primary table structure is as follows: ```sql CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name NVARCHAR(100), Position NVARCHAR(100), Salary DECIMAL(10, 2), ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory)); ``` I have been inserting and updating records successfully, but when I check the `EmployeesHistory` table, I only see entries for certain updates. For instance, after running this update: ```sql UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1; ``` I expect the previous salary to appear in the history table, but sometimes it doesn't. When I run: ```sql SELECT * FROM EmployeesHistory WHERE EmployeeID = 1; ``` I get no records back, although I can see the updated record in the `Employees` table. I have verified that the `SYSTEM_VERSIONING` is indeed enabled, and I have not altered the `ValidFrom` or `ValidTo` columns directly, which I understand is against best practices. I also checked the `ALTER TABLE` statements to ensure that changes to `SYSTEM_VERSIONING` haven't affected retention, and everything seems to be configured correctly. However, I do see a performance warning when I run a query against the history table, which looks like this: ``` Warning: The query optimizer could not perform a join due to insufficient memory. ``` Could this be affecting the retention of historical records? What could be the reason for missing entries in the history table? Any insights or suggestions would be appreciated! Is there a better approach? This is happening in both development and production on Ubuntu 22.04. The project is a microservice built with Sql.