--===========================================================================-- -- 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 2 : tables temporelles avec historisation -- --▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀-- /* DROP SCHEMA chronos; */ USE DB_TEMPORAL; GO ------------------------------------------------------------------------------- -- créations ------------------------------------------------------------------------------- CREATE TABLE Tempo4 ( DATA4 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) ) WITH (SYSTEM_VERSIONING = ON); GO --> plante ! (évidemment gros béta...) CREATE TABLE Tempo4 ( ID INT IDENTITY PRIMARY KEY, DATA4 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) ) WITH (SYSTEM_VERSIONING = ON); GO CREATE TABLE Tempo5 ( ID INT IDENTITY PRIMARY KEY, DATA4 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) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Tempo5_histo)); GO --> plante aussi... (enfin grand idiot !) CREATE TABLE Tempo5 ( ID INT IDENTITY PRIMARY KEY, DATA5 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) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Tempo5_histo)); GO CREATE SCHEMA chronos CREATE TABLE Tempo6 ( ID INT IDENTITY PRIMARY KEY, DATA6 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) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Tempo6)); GO CREATE TABLE dbo.Tempo7 ( ID INT IDENTITY PRIMARY KEY, DATA7 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) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = chronos.Tempo7)); GO ------------------------------------------------------------------------------- --> suppression : ------------------------------------------------------------------------------- -- supprime uniquement la table temporalisées (on conserver l'historique) ALTER TABLE dbo.Tempo4 SET (SYSTEM_VERSIONING = OFF); DROP TABLE dbo.Tempo4 ; GO -- suppression des deux tables ALTER TABLE dbo.Tempo5 SET (SYSTEM_VERSIONING = OFF); DROP TABLE dbo.Tempo5; DROP TABLE dbo.Tempo5_histo; GO -- suppression de l'historique et conservation de la table temporalisée ALTER TABLE chronos.Tempo6 SET (SYSTEM_VERSIONING = OFF); DROP TABLE dbo.Tempo6; GO -- suppression de l'historique, conservation de la table temporalisée mais suppression de la temporalisation ALTER TABLE dbo.Tempo7 SET (SYSTEM_VERSIONING = OFF); DROP TABLE chronos.Tempo7; ALTER TABLE dbo.Tempo7 DROP PERIOD FOR SYSTEM_TIME; ALTER TABLE dbo.Tempo7 DROP COLUMN _DH_DEB, _DH_FIN; GO ------------------------------------------------------------------------------- --> BONUS ! procédure de suppression automatique: ------------------------------------------------------------------------------- CREATE PROCEDURE dbo.sp__drop_temporal_table @schema_name sysname = 'dbo', @table_name sysname, @mode VARCHAR(16) = 'cascade all' --> sinon : 'history only' AS BEGIN SET NOCOUNT ON; DECLARE @MSG NVARCHAR(1204), @HIST_TABLE_ID INT; SET @mode = LOWER(@mode); -- did the table exists ? And is it a temporal table ?? SELECT @HIST_TABLE_ID = history_table_id FROM sys.tables AS t JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE t.name = @table_name AND s.name = COALESCE(@schema_name, 'dbo') AND temporal_type = 2; IF @HIST_TABLE_ID IS NULL BEGIN SET @MSG = N'Table ' + @schema_name + N'.' + @table_name + N' appear not to be a temporal table or not exists. If it exists, drop it manually.'; THROW 55555, @MSG, 1; RETURN END; -- mode must be : "cascade all" or "history only" IF @mode NOT IN ('cascade all', 'history only') BEGIN SET @MSG = N'Dropping a temporal table can be done either in "cascade all" mode or "history only". In "cascade all" all objects are dropped including the original table. In "history only" Temporal History table is dropped and period''s columns are dropped, while the original table is preserved.'; THROW 55555, @MSG, 1; RETURN; END; -- creating the drop code in SQL DECLARE @SQL NVARCHAR(max), @SQL2 NVARCHAR(max); BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY -- disabling auto versionning SET @SQL = N'ALTER TABLE [' + @schema_name + N'].[' + @table_name + N'] SET (SYSTEM_VERSIONING = OFF);' EXEC (@SQL); SELECT @SQL = N'DROP TABLE [' + s.name + N'].[' + t.name + N'];' FROM sys.tables AS t JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE object_id = @HIST_TABLE_ID; EXEC (@SQL); IF @mode = 'cascade all' -- table to be dropped BEGIN SET @SQL = N'DROP TABLE [' + @schema_name + N'].[' + @table_name + N'];'; EXEC (@SQL); END; IF @mode = 'history only' -- columns to be dropped BEGIN SET @SQL = N'ALTER TABLE [' + @schema_name + N'].[' + @table_name + N'] DROP PERIOD FOR SYSTEM_TIME;'; SET @SQL2 = 'ALTER TABLE [' + @schema_name + N'].[' + @table_name + N'] DROP COLUMN '; SELECT @SQL2 = @SQL2 + c.name + N', ' FROM sys.columns AS c JOIN sys.tables AS t ON c.object_id = t.object_id JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE t.name = @table_name AND s.name = COALESCE(@schema_name, 'dbo') AND c.generated_always_type IN (1, 2); SET @SQL2 = LEFT(@SQL2, LEN(@SQL2) - 1); EXEC (@SQL); EXEC (@SQL2); END; COMMIT; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK; THROW; END CATCH END; GO EXEC dbo.sp__drop_temporal_table 'dbo', 'Tempo4', 'cascade all'; GO EXEC dbo.sp__drop_temporal_table 'dbo', 'Tempo5', 'cascade all'; GO EXEC dbo.sp__drop_temporal_table 'chronos', 'Tempo6', 'cascade all'; GO EXEC dbo.sp__drop_temporal_table 'dbo', 'Tempo7', 'cascade all'; GO