Home Forums SQL Server 2014 Development - SQL Server 2014 Trying to use the HASHBYTES of a nvarcar(Max) RE: Trying to use the HASHBYTES of a nvarcar(Max)

  • July 31, 2016 at 1:19 am

    #1892797

    In this case there is no difference between an assumption and a casual observation, nothing solid to proof anything.

    😎

    Here are two test harnesses, a) measures the difference between calculating the md5 on insert vs. calculated column, b) the difference between inserting an pre-calculated md5 vs. calculated column.

    The caveat here is that the input is limited to the maximum of 4000 wide characters, in real live situation one would expect larger inputs which means more complicated calculation of the md5, especially with the calculated column as it is limited to a scalar function because the apply operator cannot be used there. That will further add to the overhead of the calculated column method.

    Test Harness A

    USE TEEST;

    GO

    SET NOCOUNT ON;

    -- Test the difference between calculating an md5 hash on the insert and a calculated column for the md5

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH_SAMPLE') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH_SAMPLE;

    CREATE TABLE dbo.TBL_TEST_HASH_SAMPLE

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_SAMPLE_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 BINARY(20) NOT NULL

    );

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH;

    CREATE TABLE dbo.TBL_TEST_HASH

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 BINARY(20) NOT NULL

    );

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH_CALC_COL') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH_CALC_COL;

    CREATE TABLE dbo.TBL_TEST_HASH_CALC_COL

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_CALC_COL_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 AS (HASHBYTES('MD5',TH_NSTR)) PERSISTED

    );

    DECLARE @SAMPLE_SIZE INT = 100000;

    DECLARE @MAX_SIZE INT = 4000;

    -- APPROX 7.5m characters

    DECLARE @LONG_STR NVARCHAR(MAX) =

    (

    SELECT

    NCHAR(32) + SMSG.text

    FROM sys.messages SMSG

    WHERE SMSG.language_id IN (1031, 1033,1036,3082,1040,2068)

    FOR XML PATH('')

    );

    DECLARE @MAX_START INT = LEN(@LONG_STR) - (@MAX_SIZE + 1);

    ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

    , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    ,STRDATA(TEST_STR) AS

    (

    SELECT

    SUBSTRING(@LONG_STR ,(ABS(CHECKSUM(NEWID())) % @MAX_START),1 + (ABS(CHECKSUM(NEWID())) % @MAX_SIZE)) AS TEST_STR

    FROM NUMS NM

    )

    INSERT INTO dbo.TBL_TEST_HASH_SAMPLE(TH_NSTR,TH_MD5)

    SELECT

    SD.TEST_STR

    ,HASHBYTES('MD5',SD.TEST_STR)

    FROM STRDATA SD;

    DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));

    DECLARE @NVARCHAR_BUCKET NVARCHAR(MAX) = N'';

    DECLARE @BINARY_BUCKET BINARY(20) = 0x00;

    -- ROUND ONE

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND TWO

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND THREE

    TRUNCATE TABLE dbo.TBL_TEST_HASH_CALC_COL;

    TRUNCATE TABLE dbo.TBL_TEST_HASH;

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND FOUR

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND FIVE

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND SIX

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND SEVEN

    TRUNCATE TABLE dbo.TBL_TEST_HASH_CALC_COL;

    TRUNCATE TABLE dbo.TBL_TEST_HASH;

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND EIGHT

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,HASHBYTES('MD5',TS.TH_NSTR)

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    SELECT

    T.T_TXT

    ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

    FROM @timer T

    GROUP BY T.T_TXT

    ORDER BY DURATION;

    Results from A on 2nd Gen i5, 8Gb ram, 3rd Gen SSD, SQL Server 2016 Dev Ed.

    T_TXT DURATION

    ----------- -----------

    INSERT 59156070

    DRY RUN 59394936

    CALC COL 67620986

    Test Harness B

    USE TEEST;

    GO

    SET NOCOUNT ON;

    -- Test the difference between inserting an md5 hash and a calculated column for the md5

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH_SAMPLE') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH_SAMPLE;

    CREATE TABLE dbo.TBL_TEST_HASH_SAMPLE

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_SAMPLE_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 BINARY(20) NOT NULL

    );

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH;

    CREATE TABLE dbo.TBL_TEST_HASH

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 BINARY(20) NOT NULL

    );

    IF OBJECT_ID(N'dbo.TBL_TEST_HASH_CALC_COL') IS NOT NULL DROP TABLE dbo.TBL_TEST_HASH_CALC_COL;

    CREATE TABLE dbo.TBL_TEST_HASH_CALC_COL

    (

    TH_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_HASH_CALC_COL_TH_ID PRIMARY KEY CLUSTERED

    ,TH_NSTR NVARCHAR(MAX) NOT NULL

    ,TH_MD5 AS (HASHBYTES('MD5',TH_NSTR)) PERSISTED

    );

    DECLARE @SAMPLE_SIZE INT = 100000;

    DECLARE @MAX_SIZE INT = 4000;

    -- APPROX 7.5m characters

    DECLARE @LONG_STR NVARCHAR(MAX) =

    (

    SELECT

    NCHAR(32) + SMSG.text

    FROM sys.messages SMSG

    WHERE SMSG.language_id IN (1031, 1033,1036,3082,1040,2068)

    FOR XML PATH('')

    );

    DECLARE @MAX_START INT = LEN(@LONG_STR) - (@MAX_SIZE + 1);

    ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

    , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

    ,STRDATA(TEST_STR) AS

    (

    SELECT

    SUBSTRING(@LONG_STR ,(ABS(CHECKSUM(NEWID())) % @MAX_START),1 + (ABS(CHECKSUM(NEWID())) % @MAX_SIZE)) AS TEST_STR

    FROM NUMS NM

    )

    INSERT INTO dbo.TBL_TEST_HASH_SAMPLE(TH_NSTR,TH_MD5)

    SELECT

    SD.TEST_STR

    ,HASHBYTES('MD5',SD.TEST_STR)

    FROM STRDATA SD;

    DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));

    DECLARE @NVARCHAR_BUCKET NVARCHAR(MAX) = N'';

    DECLARE @BINARY_BUCKET BINARY(20) = 0x00;

    -- ROUND ONE

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND TWO

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND THREE

    TRUNCATE TABLE dbo.TBL_TEST_HASH_CALC_COL;

    TRUNCATE TABLE dbo.TBL_TEST_HASH;

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND FOUR

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND FIVE

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND SIX

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    -- ROUND SEVEN

    TRUNCATE TABLE dbo.TBL_TEST_HASH_CALC_COL;

    TRUNCATE TABLE dbo.TBL_TEST_HASH;

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    -- ROUND EIGHT

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    SELECT

    @NVARCHAR_BUCKET = TS.TH_NSTR

    ,@BINARY_BUCKET = TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS

    INSERT INTO @timer(T_TXT) VALUES ('DRY RUN');

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO dbo.TBL_TEST_HASH(TH_NSTR,TH_MD5)

    SELECT

    TS.TH_NSTR

    ,TS.TH_MD5

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('INSERT');

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    INSERT INTO dbo.TBL_TEST_HASH_CALC_COL(TH_NSTR)

    SELECT

    TS.TH_NSTR

    FROM dbo.TBL_TEST_HASH_SAMPLE TS;

    INSERT INTO @timer(T_TXT) VALUES ('CALC COL');

    SELECT

    T.T_TXT

    ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION

    FROM @timer T

    GROUP BY T.T_TXT

    ORDER BY DURATION;

    Results from B on 2nd Gen i5, 8Gb ram, 3rd Gen SSD, SQL Server 2016 Dev Ed.

    T_TXT DURATION

    ---------- -----------

    INSERT 41015984

    DRY RUN 43208396

    CALC COL 49214666