Напильник и щепотка фантазии… или как слепить Enterprise из SQL Server Express Edition

USE [master]
GO

SET NOCOUNT ON
SET STATISTICS IO, TIME OFF

IF DB_ID('express') IS NOT NULL BEGIN
    ALTER DATABASE [express] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [express]
END
GO

CREATE DATABASE [express]
ON PRIMARY (NAME = N'express', FILENAME = N'D:\express.mdf', SIZE = 200 MB, FILEGROWTH = 100 MB)
    LOG ON (NAME = N'express_log', FILENAME = N'D:\express_log.ldf', SIZE = 200 MB, FILEGROWTH = 100 MB)

ALTER DATABASE [express] SET AUTO_CLOSE OFF
ALTER DATABASE [express] SET RECOVERY SIMPLE
ALTER DATABASE [express] SET MULTI_USER
ALTER DATABASE [express] SET DELAYED_DURABILITY = ALLOWED
ALTER DATABASE [express] ADD FILEGROUP [MEM] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [express] ADD FILE (NAME = 'MEM', FILENAME = 'D:\MEM') TO FILEGROUP [MEM]
ALTER DATABASE [express] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
GO 

USE [express]
GO

DROP TABLE IF EXISTS [CCL]
CREATE TABLE [CCL] (
      [INT]      INT NOT NULL
    , [VARCHAR]  VARCHAR(100) NOT NULL
    , [DATETIME] DATETIME NOT NULL
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX ON [CCL] WITH (DATA_COMPRESSION = COLUMNSTORE)
GO

DECLARE @i INT = 0

lbl:
    ;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
        , E2(N) AS (SELECT '1' FROM E1 a, E1 b)
        , E4(N) AS (SELECT '1' FROM E2 a, E2 b)
        , E8(N) AS (SELECT '1' FROM E4 a, E4 b)
    INSERT INTO [CCL] WITH(TABLOCK) ([INT], [VARCHAR], [DATETIME])
    SELECT TOP(5000000)
          ROW_NUMBER() OVER (ORDER BY 1/0)
        , CAST(ROW_NUMBER() OVER (ORDER BY 1/0) AS VARCHAR(100))
        , DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY 1/0) % 100, '20180101')
    FROM E8

    SET @i += 1

IF @i < 20 GOTO lbl

SELECT 'DATA', CAST(SUM(size_in_bytes) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.indexes i
CROSS APPLY sys.fn_column_store_row_groups(i.[object_id]) s
WHERE i.[type] IN (5, 6)

UNION ALL

SELECT 'DICT', CAST(SUM(on_disk_size) / (1024. * 1024) AS DECIMAL(18,2))
FROM sys.column_store_dictionaries
GO

DROP TABLE IF EXISTS [MEM]
CREATE TABLE [MEM] (A INT PRIMARY KEY NONCLUSTERED, B DATETIME INDEX IX2 NONCLUSTERED, C VARCHAR(4000))
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

DECLARE @i INT
      , @s DATETIME
      , @runs INT = 10

SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs BEGIN

    ;WITH E1(N) AS (SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N))
        , E2(N) AS (SELECT '1' FROM E1 a, E1 b)
        , E4(N) AS (SELECT '1' FROM E2 a, E2 b)
        , E8(N) AS (SELECT '1' FROM E4 a, E4 b)
    INSERT INTO [MEM] ([A], [B], [C])
    SELECT TOP(500000)
          ROW_NUMBER() OVER (ORDER BY 1/0) + (@i * 1000000)
        , GETDATE()
        , NEWID()
    FROM E8

    SET @i += 1
END

SELECT *
FROM sys.dm_db_xtp_table_memory_stats