snippetsqlMinor
How to check if the SYSTEM_VERSIONING for a table is ON?
Viewed 0 times
thesystem_versioningforhowchecktable
Problem
I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :
Also to deactivate this, it is only necessary to ALTER the table:
My question is how to check if the SYSTEM_VERSIONING for a table is ON and then ALTER the table?
CREATE TABLE EmpSalary
(
EmpID int NOT NULL PRIMARY KEY CLUSTERED
, SalaryAmt decimal (10,2) NULL
, SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT)
)
WITH (SYSTEM_VERSIONING = ON);Also to deactivate this, it is only necessary to ALTER the table:
ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );My question is how to check if the SYSTEM_VERSIONING for a table is ON and then ALTER the table?
Solution
You could query
sys.tablesALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'
| name | temporal_type | temporal_type_desc |
|-----------|---------------|---------------------------------|
| EmpSalary | 2 | SYSTEM_VERSIONED_TEMPORAL_TABLE |ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'
| name | temporal_type | temporal_type_desc |
|-----------|---------------|--------------------|
| EmpSalary | 0 | NON_TEMPORAL_TABLE |Code Snippets
ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'
| name | temporal_type | temporal_type_desc |
|-----------|---------------|---------------------------------|
| EmpSalary | 2 | SYSTEM_VERSIONED_TEMPORAL_TABLE |ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'
| name | temporal_type | temporal_type_desc |
|-----------|---------------|--------------------|
| EmpSalary | 0 | NON_TEMPORAL_TABLE |Context
StackExchange Database Administrators Q#218517, answer score: 8
Revisions (0)
No revisions yet.