System Versioned Temporal Tables in SQL Server 2016, 2019 with Ex to Insert, Update, Delete, & Drop
Hello Guys,
In this video tutorial, I have discussed how to use temporal tables in SQL Server 2016. Nowadays, It is a useful feature in the database for keeping the complete history of a table changes over the period of time. The system-Versioned table allows us to make decisions for making our application system better from time to time as it allows us to do the deep analysis of data changes with the system start time and sys end time columns.
Temporal or History table also allows us to restore data from the history table as this table is used to keep all updates and delete changes done in a table's row.
The temporal table is not a normal database table which we can delete by using simply the Drop Table TableName command. To drop system-versioned temporal tables we first need to set the system versioning off and then we can use the 'Drop Table HistoryTableName' command.
You will also learn here to use the Temporal table by performing Insert, Update, Delete and Drop operations on the History table.
System-Version Temporal Table SQL Query URL:
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED
, DeptName VARCHAR(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID INT NULL
, SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
Go
Insert into Department
(DeptID, DeptName, ManagerID, ParentDeptID)
values (1, 'HR', 23, 2)
Go
Update Department SET DeptName = 'IT' Where DeptID = 1
Go
Delete from Department where DeptID = 1
Go
ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[DepartmentHistory]
GO
Go to this link to take the test to identify yourself.
https://oktests.com/
Like us on Facebook: / oktests