text: ﻿/*
  Author: Konstantin Taranov
  Link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2014.sql
  */
  USE master;
  GO
  
  DECLARE @databaseFilePath NVARCHAR(1000) = N'';
  SELECT @databaseFilePath = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
   FROM  master.sys.master_files
   WHERE database_id = 1 AND file_id = 1;
  DECLARE @databaseName     SYSNAME        = N'ಠ ಠ 14 Test';
  DECLARE @tsqlStatement    NVARCHAR(4000) = N'';
  DECLARE @debug            BIT            = 0;
  
  SET @tsqlStatement = '
  IF DB_ID(N''@databaseName'') IS NOT NULL
  BEGIN
      ALTER DATABASE [@databaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      DROP DATABASE  [@databaseName];
  END;
  
  CREATE DATABASE [@databaseName]
   CONTAINMENT = NONE
   ON PRIMARY
  (NAME = N''@databaseName'', FILENAME = N''@databaseFilePath@databaseName.mdf'', SIZE = 64MB, MAXSIZE = UNLIMITED, FILEGROWTH = 64MB),
  FILEGROUP [@databaseName] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
  ( NAME = N''@databaseName_mod'', FILENAME = N''@databaseFilePath@databaseName_mod'', MAXSIZE = UNLIMITED)
   LOG ON
  ( NAME = N''@databaseName_log'', FILENAME = N''@databaseFilePath@databaseName_log.ldf'', SIZE = 64MB, MAXSIZE = 2048MB, FILEGROWTH = 64MB);
  
  ALTER DATABASE [@databaseName] SET COMPATIBILITY_LEVEL = 120;
  ';
  
  SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseName',     @databaseName);
  SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseFilePath', @databaseFilePath);
  
  IF @debug = 1 PRINT(@tsqlStatement)
  ELSE
  EXEC sp_executesql @tsqlStatement;
  
  
  SET @tsqlStatement = '
  USE [@databaseName];
  --UNCOMMENT GO Statement if you want to execute statements after printing in debug mode!!!
  --GO
  
  -- configure recommended DB option
  ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
  
  -- memory-optimized table
  CREATE TABLE dbo.table1(
      c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
      c2 NVARCHAR(4000)
  )
  WITH (MEMORY_OPTIMIZED=ON);
  
  -- non-durable table
  CREATE TABLE dbo.temp_table1(
      c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
      c2 NVARCHAR(4000)
  )
  WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);
  
   -- memory-optimized table type
  CREATE TYPE dbo.tt_table1 AS TABLE(
      c1 INT IDENTITY,
      c2 NVARCHAR(4000),
      is_transient BIT NOT NULL DEFAULT (0),
      INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
  WITH (MEMORY_OPTIMIZED=ON);
  
  CREATE TABLE dbo.InMemTable1(
       keyColumn INT IDENTITY PRIMARY KEY NONCLUSTERED
     , description NCHAR(100) NOT NULL
  )
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
  
  INSERT dbo.InMemTable1(description)
  VALUES
   (REPLICATE(''A'', 100))
  ,(REPLICATE(''B'', 100))
  ,(REPLICATE(''C'', 100))
  ,(REPLICATE(''D'', 100))
  ,(REPLICATE(''E'', 100))
  ,(REPLICATE(''F'', 100));
  ';
  
  SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseName', @databaseName);
  
  IF @debug = 1 PRINT(@tsqlStatement)
  ELSE
  EXEC sp_executesql @tsqlStatement;
  
  
  -- https://stackoverflow.com/a/793362/2298061
  DECLARE @UseAndExecStatment NVARCHAR(4000);
  SET @UseAndExecStatment = N'USE [' + @databaseName + N']; EXEC sp_executesql @tsqlStatement';
  
  SET @tsqlStatement = N'
  CREATE PROCEDURE dbo.native_sp
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
  AS
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N''us_english'')
      SELECT keyColumn
           , description
      FROM dbo.InMemTable1;
  END;
  --EXECUTE dbo.native_sp;';
  
  IF @debug = 1 PRINT(@tsqlStatement)
  ELSE
  BEGIN
  EXEC sp_executesql @UseAndExecStatment,
       N'@tsqlStatement NVARCHAR(MAX)', @tsqlStatement = @tsqlStatement;
  END;
  