Hello, again reader…
Today, I will discuss a gotcha I ran across with SQL Server Temporal Tables. In my day-to-day environment, we do not use Temporal Tables widely. However, a few are in use, and a few are coming down the pipeline for new projects.
If you have not heard of Temporal Tables, you can review the information from Microsoft. If you are interested in trying these out, they did not show up on the SQL Server scene until 2016.
This past week I was asked to promote schema and data from a new development database to a testing database. The steps given were the following.
- Compare schema from dev to test for a specific schema
- Truncate data in all tables in the test for a specific schema
- Migrate all data in dev to test for a specific schema
Look and sound pretty straightforward. I open RedGate SQL Compare enter the source and destination systems, and bam, number one is done.
Next, let me run the truncate script on the test database.
Uh oh, a red error message is never good.
It appears that a truncate table command is not compatible with Temporal Tables. So after a bit of research, I found you can run the following code to get past the issue.
ALTER TABLE [Security].[UserAccess] SET (SYSTEM_VERSIONING = OFF); Truncate Table [Security].[UserAccess]; ALTER TABLE [Security].[UserAccess] SET ( SYSTEM_VERSIONING = ON );
Let me explain what we have going on here. First, we have to turn off temporal system versioning on the table. Then we can truncate the table as we tried initially. Finally, we turn system versioning back on for the table.
It is not as straightforward as a standard table, but there are ways to accomplish the truncate command.
Don’t get me started about foreign keys. 
The post SQL Temporal Tables — Gotcha!!! appeared first on GarryBargsley.com.