13

I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :

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?

MDCCL
  • 8,530
  • 3
  • 32
  • 63
eandbsoftware
  • 131
  • 1
  • 1
  • 3

2 Answers2

9

You could query sys.tables

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 |
Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52
8

Alternatively, you could check its OBJECTPROPERTY: TableTemporalType.

The following code will perform a logical check to see if it is a Temporal Table, and if so, deactivate System Versioning.

IF OBJECTPROPERTY(OBJECT_ID('EmpSalary'), 'TableTemporalType') = 2
    ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF)
solutionist
  • 351
  • 2
  • 6