MSSQL 2016 Temporal tables EXAMPLE

gistfile1.txt

-- drop
if object_id ('dbo.person') is not null
begin
	ALTER TABLE dbo.person SET (SYSTEM_VERSIONING = OFF)
	DROP TABLE dbo.person
end
 
if object_id ('dbo.person_history') is not null
DROP TABLE dbo.person_history

if object_id ('SEQ_person') is not null
	drop sequence SEQ_person;

---- create 

create sequence SEQ_person as INTEGER START WITH 1 INCREMENT BY 1 ;

CREATE TABLE dbo.person(
	id int primary key clustered default  NEXT VALUE FOR SEQ_person
	,name varchar(200) NULL
	,sex char(1) NULL
	,tabnum varchar(10) NULL
	,update_time datetime

    ,start_time datetime2 GENERATED ALWAYS AS ROW START 
          -- CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()  
    ,end_time datetime2 GENERATED ALWAYS AS ROW END 
           --CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),   
      ,PERIOD FOR SYSTEM_TIME (start_time, end_time)
) ON [PRIMARY]
with (
	SYSTEM_VERSIONING = ON ( HISTORY_TABLE=dbo.person_history )
 ) 

GO

--- insert update 

insert into dbo.person
(
	name, sex, tabnum
)
select 'alex','f','123asd'
;

update dbo.person
set tabnum = '2233444'
where id = 1 ;

update dbo.person
set tabnum = '5657'
where id = 1 ;

select * 
from 
	dbo.person_history
go