--===========================================================================--
-- TABLES temporelles  /   MS Cloud Summit 2017   /  2017-01-09  version 1.0 --
--===========================================================================--
-- Fred Brouard, SQLpro -  http://sqlpro.developpez.com - SQLpro@SQLspot.com --
-- Arian papillon -        http://www.datafly.fr/ -   a.papillon@datafly.com --
--===========================================================================--

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- Exemples 1 :tables temporelles sans historisation                         --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--


/*
DROP TABLE dbo.tempo1;
DROP TABLE dbo.tempo2;
DROP TABLE dbo.tempo3;
*/

CREATE DATABASE DB_TEMPORAL;
GO

USE  DB_TEMPORAL;
GO

--> 1
CREATE TABLE tempo1 
(
DATA1 VARCHAR(32), 
_DH_DEB DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
_DH_FIN DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (_DH_DEB, _DH_FIN)
);
GO

CREATE TABLE tempo2
(
DATA2 VARCHAR(32),
_DH_DEB DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),
_DH_FIN DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL DEFAULT '9999-12-31T23:59:59.9999999',
PERIOD FOR SYSTEM_TIME (_DH_DEB, _DH_FIN)
);
GO

CREATE TABLE tempo3
(
DATA3 VARCHAR(32),
_DH_DEB DATETIME2 GENERATED ALWAYS AS ROW START,
_DH_FIN DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (_DH_DEB, _DH_FIN)
);
GO


--> 2
INSERT INTO tempo1 VALUES ('111')
GO
INSERT INTO tempo2 VALUES ('222')
GO
INSERT INTO tempo3 VALUES ('333')
GO
INSERT INTO tempo3 VALUES ('333', '2013-01-22', '2016-02-21')
GO
INSERT INTO tempo3 (DATA3) VALUES ('333')
GO

--> 3
SELECT * FROM tempo1;
SELECT DATA1, _DH_DEB, _DH_FIN FROM tempo1;
SELECT * FROM tempo2;
SELECT DATA2, _DH_DEB, _DH_FIN FROM tempo2;
SELECT * FROM tempo3;
WAITFOR DELAY '00:00:03';
UPDATE tempo1 SET DATA1 = NULL;
SELECT * FROM tempo1;
GO