--===========================================================================--
-- 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 7 : "in Memory"                                                  --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--

/*
USE master;
DROP DATABASE DB_TEMPORAL_HEKATON;
GO
*/

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

EXEC xp_cmdshell 'MKDIR "C:\DATABASES\SQLSERVER\DEMO_TEMPORAL_IN_MEMORY\"';
GO

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
GO


CREATE DATABASE DB_TEMPORAL_HEKATON
 ON  PRIMARY
   ( NAME       = N'DB_TEMPORAL_HEKATON', 
     FILENAME   = N'C:\DATABASES\SQLSERVER\DEMO_TEMPORAL_IN_MEMORY\DB_TEMPORAL_HEKATON.mdf' , 
     SIZE       = 8192KB , 
     FILEGROWTH = 8192KB ),
FILEGROUP [HekatonFG] CONTAINS MEMORY_OPTIMIZED_DATA 
   ( NAME       = N'HekatonFile', 
     FILENAME   = N'C:\DATABASES\SQLSERVER\DEMO_TEMPORAL_IN_MEMORY\DB_TEMPORAL_HekatonFile' )
LOG ON 
   ( NAME       = N'DB_TEMPORAL_HEKATON_log', 
     FILENAME   = N'C:\DATABASES\SQLSERVER\DEMO_TEMPORAL_IN_MEMORY\DB_TEMPORAL_HEKATON_log.ldf' ,
     SIZE       = 8192KB , 
     FILEGROWTH = 8192KB )
GO


USE DB_TEMPORAL_HEKATON
GO

CREATE TABLE TempoHekaton
(
ID            INT IDENTITY PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 6000000),
DATAHK        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.TempoHekaton_histo),
      MEMORY_OPTIMIZED  = ON, 
      DURABILITY = SCHEMA_AND_DATA);
GO

-- Table temporelle et son historique
SELECT  name, is_memory_optimized, temporal_type
FROM    sys.tables
WHERE   temporal_type IN ( 1, 2 );
-- Table de staging in-memory
SELECT  SCHEMA_NAME(schema_id) AS schema_nama, name AS table_name, internal_type_desc
FROM    sys.internal_tables
WHERE   parent_object_id IN ( SELECT    object_id
                              FROM      sys.tables
                              WHERE     temporal_type = 2 );
GO

-- insérer des données 
CREATE PROC sp_insertdata -- natively compiled procedure
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
      LANGUAGE = 'us_english');
	DECLARE @Num int = 1;
	WHILE @Num  <= 5000000
	BEGIN
		INSERT INTO dbo.TempoHekaton VALUES (CAST(@Num AS VARCHAR(6))+' row...');
		SET @Num += 1;
	END
END;
GO    

EXEC sp_insertdata;
GO

-- mettre à jour des lignes (mode interop)
UPDATE dbo.TempoHekaton
SET    DATAHK=DATAHK+' updated' 
WHERE  ID % 18 = 0;
-- UPDATE dbo.TempoHekaton SET DataHK = CAST(ID AS VARCHAR(6))+' row...' WHERE ID % 18 = 0

-- Performances pour un SELECT
SELECT * 
FROM   TempoHekaton FOR SYSTEM_TIME ALL 
WHERE  ID = 10000

-- Voyons les index
EXEC sp_helpindex 'TempoHekaton_histo';
EXEC sp_helpindex 'sys.memory_optimized_history_table_565577053';

-- Améliorons
CREATE NONCLUSTERED INDEX ix_TempoHekaton_histo_ID 
   ON TempoHekaton_histo(ID);

-- Avec SQL Server 2016 SP1 ou 2016 CU3 : ajout d'index sur la table interne
DBCC TRACEON (10316)
GO
ALTER TABLE sys.memory_optimized_history_table_565577053
   ADD INDEX NCI_IndID (ID)
GO
DBCC TRACEOFF (10316)
GO

-- voyons
SELECT * 
FROM   TempoHekaton FOR SYSTEM_TIME ALL 
WHERE  ID = 10000

/*
DROP INDEX ix_TempoHekaton_histo_ID ON TempoHekaton_histo
ALTER TABLE sys.memory_optimized_history_table_885578193 DROP INDEX NCI_IndID
*/

-- Consommation mémoire
WITH 
InMemoryTemporalTables AS
(
SELECT  SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema
       ,T1.object_id AS TemporalTableObjectId
       ,IT.object_id AS InternalTableObjectId
       ,OBJECT_NAME (IT.parent_object_id) AS ParentTemporalTableName
       ,IT.name AS InternalHistoryStagingName
FROM   sys.internal_tables IT
       JOIN sys.tables T1 
            ON IT.parent_object_id = T1.object_id
WHERE  T1.is_memory_optimized = 1 
  AND  T1.temporal_type = 2
), 
DetailedConsumption AS
(
SELECT  TemporalTableSchema
       ,T.ParentTemporalTableName
       ,T.InternalHistoryStagingName
       ,CASE
           WHEN C.object_id = T.TemporalTableObjectId
              THEN 'Temporal Table Consumption'
           ELSE 'Internal Table Consumption'
        END ConsumedBy
       ,C.*
FROM   sys.dm_db_xtp_memory_consumers C
       JOIN InMemoryTemporalTables T
            ON C.object_id = T.TemporalTableObjectId 
            OR C.object_id = T.InternalTableObjectId
)
--select * from DetailedConsumption
SELECT TemporalTableSchema,
       ParentTemporalTableName, object_id, object_name(object_id) as MemoryUsedByTable
       , sum ( allocated_bytes ) AS allocated_bytes
       , sum ( used_bytes ) AS used_bytes
FROM   DetailedConsumption
GROUP  BY TemporalTableSchema, ParentTemporalTableName, InternalHistoryStagingName, object_id;