Crud Tests Performance

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE IF EXISTS CrudTest_TempTable;
GO

CREATE PROCEDURE CrudTest_TempTable @InsertsCount int, @UpdatesCount int, @DeletesCount int
AS
BEGIN

    SET NOCOUNT ON;
    BEGIN TRAN;

    CREATE TABLE #tempTable
    (
        Col1 INT NOT NULL PRIMARY KEY CLUSTERED,
        Col2 NVARCHAR(4000),
        Col3 NVARCHAR(4000),
        Col4 DATETIME2,
        Col5 INT NOT NULL
    );

    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();


    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO #tempTable (Col1, Col2, Col3, Col4, Col5)
        VALUES (@cnt,
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE #tempTable SET Col5 = 101 WHERE Col1 = cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM #tempTable WHERE Col1 = cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

    COMMIT;
END
GO

DROP PROCEDURE IF EXISTS CrudTest_SpidFilter_memopt_hash;
GO

DROP SECURITY POLICY IF EXISTS tempTable_memopt_hash_SpidFilter_Policy;
GO

DROP TABLE IF EXISTS tempTable_SpidFilter_memopt_hash;
GO

DROP FUNCTION IF EXISTS fn_SpidFilter;
GO

CREATE FUNCTION fn_SpidFilter(@SpidFilter smallint)
    RETURNS TABLE
    WITH SCHEMABINDING , NATIVE_COMPILATION
AS
    RETURN
        SELECT 1 AS fn_SpidFilter
            WHERE @SpidFilter = @@spid;
GO

CREATE TABLE tempTable_SpidFilter_memopt_hash
(
    Col1 INT NOT NULL,
    Col2 NVARCHAR(4000),
    Col3 NVARCHAR(4000),
    Col4 DATETIME2,
    Col5 INT NOT NULL,

    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),
    INDEX ix_hash HASH (Col1, SpidFilter) WITH (BUCKET_COUNT=100000),
    CONSTRAINT CHK_SpidFilter CHECK ( SpidFilter = @@spid )
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO


CREATE SECURITY POLICY tempTable_memopt_hash_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)
    ON dbo.tempTable_SpidFilter_memopt_hash
    WITH (STATE = ON);
GO

CREATE PROCEDURE CrudTest_SpidFilter_memopt_hash @InsertsCount int, @UpdatesCount int, @DeletesCount int
AS
BEGIN

    SET NOCOUNT ON;
    BEGIN TRAN;

    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();

    DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);

    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO tempTable_SpidFilter_memopt_hash(Col1, Col2, Col3, Col4, Col5)
        VALUES (@IdxStart + @cnt,
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE tempTable_SpidFilter_memopt_hash SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM tempTable_SpidFilter_memopt_hash WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

    DELETE FROM tempTable_SpidFilter_memopt_hash;
    COMMIT;
END
GO

DROP PROCEDURE IF EXISTS CrudTest_memopt_hash;
GO

DROP TABLE IF EXISTS tempTable_memopt_hash;
GO

CREATE TABLE dbo.tempTable_memopt_hash
(
    Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
    Col2 NVARCHAR(4000),
    Col3 NVARCHAR(4000),
    Col4 DATETIME2,
    Col5 INT NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE PROCEDURE CrudTest_memopt_hash @InsertsCount int, @UpdatesCount int, @DeletesCount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();

    DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);

    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO dbo.tempTable_memopt_hash(Col1, Col2, Col3, Col4, Col5)
        VALUES (@IdxStart + @cnt,
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE dbo.tempTable_memopt_hash SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM dbo.tempTable_memopt_hash WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

    DELETE FROM dbo.tempTable_memopt_hash;
END
GO

DROP PROCEDURE IF EXISTS CrudTest_tableVar;
DROP TYPE IF EXISTS dbo.tableVar;
GO

CREATE TYPE dbo.tableVar
    AS TABLE
    (
        Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000),
        Col2 NVARCHAR(4000),
        Col3 NVARCHAR(4000),
        Col4 DATETIME2,
        Col5 INT NOT NULL
    ) WITH (MEMORY_OPTIMIZED = ON);
GO

CREATE PROCEDURE CrudTest_tableVar @InsertsCount int, @UpdatesCount int, @DeletesCount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')

    DECLARE @cnt INT = 0;
    DECLARE @currDate DATETIME2 = GETDATE();

    DECLARE @IdxStart INT = CAST ((rand() * 1000) AS INT);
    DECLARE @tvTable dbo.tableVar;

    WHILE @cnt < @InsertsCount
    BEGIN
        INSERT INTO @tvTable(Col1, Col2, Col3, Col4, Col5)
        VALUES (@IdxStart + @cnt,
        'sdkfjsdjfksjvnvsanlknc kcsmksmk ms mvskldamvks mv kv al kvmsdklmsdkl mal mklasdmf kamfksam kfmasdk mfksamdfksafeowa fpmsad lak',
        'msfkjweojfijm  skmcksamepi  eisjfi ojsona npsejfeji a piejfijsidjfai  spfdjsidjfkjskdja kfjsdp fiejfisjd pfjsdiafjisdjfipjsdi s dfipjaiesjfijeasifjdskjksjdja sidjf pajfiaj pfsdj pidfe',
        @currDate, 100);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @UpdatesCount
    BEGIN
        UPDATE @tvTable SET Col5 = 101 WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END
    SET @cnt = 0;

    WHILE @cnt < @DeletesCount
    BEGIN
        DELETE FROM @tvTable WHERE Col1 = @IdxStart + cast ((rand() * @InsertsCount) as int);

        SET @cnt = @cnt + 1;
    END

END
GO