HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to check if the SYSTEM_VERSIONING for a table is ON?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thesystem_versioningforhowchecktable

Problem

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?

Solution

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 |

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.