IN_Memory_Example_2017.sql

/*
Author: Konstantin Taranov
Link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/IN_Memory_Example_2017.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'1ಠಠಠ 16+ 错误的名字! Wrong Name! गलत नाम! Неправильное название! Nombre incorrecto! Falscher Name! 間違った名前! שם שגוי! Mauvais Nom!1';
/* To prevent Msg 5135, שםשגוי cannot be used for FILESTREAM files */
DECLARE @dbFileName_mod   SYSNAME        = SUBSTRING(@databaseName, 1, 80) + N'_mod';
DECLARE @databaseFileName NVARCHAR(1000) = REPLACE(@databaseName, N' ', N'');
DECLARE @tsqlStatement    NVARCHAR(4000) = N'';
DECLARE @crlf             VARCHAR(10) = CHAR(10) + CHAR(13);
DECLARE @debug            BIT            = 1;

SET @tsqlStatement = N'
USE master;' +
CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END +
N'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@databaseFileName.mdf'', SIZE = 32MB, MAXSIZE = 2048MB, FILEGROWTH = 64MB),
FILEGROUP [@databaseName] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N''@databaseName_mod'', FILENAME = N''@databaseFilePath@dbFileName_mod'', MAXSIZE = 2048MB)
 LOG ON
( NAME = N''@databaseName_log'', FILENAME = N''@databaseFilePath@databaseFileName_log.ldf'', SIZE = 32MB, MAXSIZE = 2048MB, FILEGROWTH = 64MB);
' + CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END +
N'IF CAST(SERVERPROPERTY(''ProductMajorVersion'') AS INT) = 14
ALTER DATABASE [@databaseName] SET COMPATIBILITY_LEVEL = 140;

IF CAST(SERVERPROPERTY(''ProductMajorVersion'') AS INT) = 13
ALTER DATABASE [@databaseName] SET COMPATIBILITY_LEVEL = 130;';

SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseName',     @databaseName);
SET @tsqlStatement = REPLACE(@tsqlStatement, '@dbFileName_mod',   @dbFileName_mod);
SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseFileName', @databaseFileName);
SET @tsqlStatement = REPLACE(@tsqlStatement, '@databaseFilePath', @databaseFilePath);

IF @debug = 1 PRINT(@tsqlStatement)
ELSE
EXEC sp_executesql @tsqlStatement;


SET @tsqlStatement = N'USE [@databaseName];' +
CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END +
N'/* configure recommended DB option */
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;

/* memory-optimized table */
CREATE TABLE dbo.[¯\_(ツ)_/¯ 😁__間違った名前 In Memory Table Я не советую так называть__👻 ¯\_(ツ)_/¯]
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);

/* non-durable table */
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);

/* memory-optimized table type */
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);

CREATE TABLE dbo.[¯\_(ツ)_/¯ 😁__間違った名前 In Memory Table Я не советую так называть__👻 ¯\_(ツ)_/¯](
      keyColumn INT IDENTITY PRIMARY KEY NONCLUSTERED
    , description CHAR(100) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT dbo.[¯\_(ツ)_/¯ 😁__間違った名前 In Memory Table Я не советую так называть__👻 ¯\_(ツ)_/¯]
(
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 = CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END +
N'/* natively compiled stored procedure */
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N''us_english'')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END
END;';

IF @debug = 1 PRINT(@tsqlStatement)
ELSE
BEGIN
EXEC sp_executesql @UseAndExecStatment,
     N'@tsqlStatement NVARCHAR(MAX)', @tsqlStatement = @tsqlStatement;
END;


SET @UseAndExecStatment = N'USE [' + @databaseName + N']; EXEC sp_executesql @tsqlStatement';
SET @tsqlStatement = CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END +
N'
CREATE OR ALTER 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.[¯\_(ツ)_/¯ 😁__間違った名前 In Memory Table Я не советую так называть__👻 ¯\_(ツ)_/¯];
END;' + CASE WHEN @debug = 1 THEN @crlf + N'GO' + @crlf ELSE N'' END;

IF @debug = 1 PRINT(@tsqlStatement)
ELSE
BEGIN
EXEC sp_executesql @UseAndExecStatment,
     N'@tsqlStatement NVARCHAR(MAX)', @tsqlStatement = @tsqlStatement;
END;


SET @UseAndExecStatment = N'USE [' + @databaseName + N']; EXEC sp_executesql @tsqlStatement';
SET @tsqlStatement = '
/* sample execution of the proc */
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N''sample durable'', 0);
INSERT @table1 (c2, is_transient) VALUES (N''sample non-durable'', 1);

EXECUTE dbo.usp_ingest_table1 @table1=@table1

SELECT c1, c2 from dbo.table1
SELECT c1, c2 from dbo.temp_table1;

EXECUTE dbo.native_sp;
';

IF @debug = 1 PRINT(@tsqlStatement)
ELSE
BEGIN
EXEC sp_executesql @UseAndExecStatment,
     N'@tsqlStatement NVARCHAR(MAX)', @tsqlStatement = @tsqlStatement;
END;