Объедините столбцы таблицы в одно уникальное значение (в качестве входных данных для HASHBYTES)

Цель

Я хочу объединить четыре столбца строки в одно значение с именем COMBINED_VALUE.

Применяются следующие строгие правила:

• Две строки с одинаковыми значениями столбцов в одном и том же порядке должны давать одинаковый результат COMBINED_VALUE
• Никакие две разные строки не могут давать одинаковые COMBINED_VALUE
• Порядок столбцов имеет значение, конечные пробелы имеют значение, невидимые символы имеют значение, любая разница имеет значение.
• Все столбцы имеют значение NULL, один столбец определяется как INT, три столбца определяются как NVARCHAR(100).

Примечания

Среди первых вещей, которые я попробовал, было BINARY_CHECKSUM. Это не удается, в результате чего для разных строк получается одно и то же значение:

SELECT
    a, b,
    binary_checksum_failing = BINARY_CHECKSUM(a,b)
FROM
    (VALUES ('i01', CONVERT(DECIMAL(9,2), 100)),
            ('i01', CONVERT(DECIMAL(9,2), 10)),
            ('i01', CONVERT(DECIMAL(9,2),  1)) )v(a, b)

HASHBYTES с «SHA2_256» работает лучше, но для этого нужен хорошо подготовленный COMBINED_VALUE в качестве входных данных. Если это обеспечено, у него есть шанс создать «очень» уникальные хэши. Я обнаружил, что объединение столбцов должно работать, но необходимо заменить NULL и разделить столбцы.

Ниже вы найдете два фрагмента кода, показывающие мой текущий прогресс и дополнительные примечания. В первой части показано решение, основанное на преобразовании в NVARCHAR, во второй части используется VARBINARY. В строках примеров показаны крайние случаи и попытка спровоцировать сбой.

Вопросы

• Сможете ли вы сделать так, чтобы представленные сценарии не сработали? Можете ли вы создать две разные строки, которые будут выдавать одно и то же COMBINED_VALUE при прохождении проверки?
• Есть ли лучший способ получить COMBINED_VALUE? Есть ли более быстрый способ? Какова лучшая практика в целом?

Сценарий №1:

DROP TABLE IF EXISTS #t

CREATE TABLE #t
(
    id           INT IDENTITY(1,1)
    ,DESCRIPTION NVARCHAR(100)
    ,number      INT
    ,word1       NVARCHAR(100)
    ,word2       NVARCHAR(100)
    ,word3       NVARCHAR(100)
)

INSERT INTO #t(DESCRIPTION, number, word1, word2, word3)
VALUES
 ('base row'                          , 1,        'aa',        'aa',     'aa') 
,('trailing space in different places', 1,        'aa',        'aa',    'aa ') 
,('trailing space in different places', 1,        'aa',       'aa ',     'aa') 
,('NULLs in different places'         , 1,       NULL,         'aa',     'aa') 
,('NULLs in different places'         , 1,        'aa',        NULL,     'aa') 
,('different rows, screwery with NULL', 1,        'aa',        'aa',     NULL) 
,('different rows, screwery with NULL', 1,        'aa',        'aa', '*NULL*') 
,('different rows, screwery with sep.', 1, 'aa'       , 'aa/SEP\aa',     'aa') 
,('different rows, screwery with sep.', 1, 'aa/SEP\aa',        'aa',     'aa') 
,('longer values'
  , 1111111111, 'abcdefghijklmn', 'abcdefghijklmn', 'abcdefghijklmn') 


DECLARE 
    @NULL_replacement NVARCHAR(10) = '*NULL*'
    ,@separator       NVARCHAR(10) = '/SEP\'

;WITH cte AS
(
    SELECT
        id, DESCRIPTION, number,
        word1, word2, word3,
        COMBINED_VALUE,
        valid_f
    FROM 
        #t
    OUTER APPLY
        (SELECT valid_f = IIF(COUNT(*) = 0, 1, 0)
         FROM (VALUES  (word1)
                      ,(word2)
                      ,(word3) )v(v)
         JOIN (VALUES  (@NULL_replacement)
                      ,(@separator       ) )w(w)
           ON v LIKE '%' + w + '%' COLLATE Czech_BIN2 
        )validator
    OUTER APPLY 
        (SELECT COMBINED_VALUE = CONCAT_WS(
              @separator
              ,COALESCE(CONVERT(NVARCHAR(100), number), @NULL_replacement) 
              ,COALESCE(CONVERT(NVARCHAR(100), word1 ), @NULL_replacement) 
              ,COALESCE(CONVERT(NVARCHAR(100), word2 ), @NULL_replacement) 
              ,COALESCE(CONVERT(NVARCHAR(100), word3 ), @NULL_replacement)) 
        )combiner
)
SELECT
    id
    ,DESCRIPTION
    ,number
    ,word1_encapsulated          = '>' + word1 + '<'
    ,word2_encapsulated          = '>' + word2 + '<'
    ,word3_encapsulated          = '>' + word3 + '<'
    ,COMBINED_VALUE_encapsulated = '>' + COMBINED_VALUE + '<'
    ,valid_f
    ,repeats
    ,COMBINED_VALUE_dlen = DATALENGTH(COMBINED_VALUE)
FROM
    cte o
OUTER APPLY
    (SELECT repeats = COUNT(*) 
     FROM cte i
     WHERE o.COMBINED_VALUE LIKE i.COMBINED_VALUE COLLATE Czech_BIN2 
    )repeatfinder
ORDER BY 
    id

Я считаю приведенный выше пример доказательством того, что мне нужно заменить NULL и использовать разделитель. См. строки 4 и 5. Если эти столбцы объединить с помощью CONCAT, вы получите тот же результат. Подробности смотрите в рубрике DESCRIPTION.

NULL замена и разделитель

• Не допускайте формирования одинаковых строк из разных строк COMBINED_VALUE
• Должно быть запрещено как часть ввода. Это необходимо проверить, входные данные должны быть продезинфицированы.
• Очень маловероятно, что это будет частью входных данных, должно быть «редко».

Скрипт №2

После вышесказанного я придумал во многом похожий запрос, в котором VARBINARY используется для уменьшения размера вывода, особенно для числовых данных. Замена нуля и разделитель встречаются «очень редко». Коды ASCII для них помечены как «неиспользуемые» в таблице ASCII.

DECLARE 
    @separator_bin VARBINARY(5) = 
      CONVERT(VARBINARY(5), CHAR(129)+CHAR(141)+CHAR(143)+CHAR(144)+CHAR(157))
    ,@NULL_repl_bin VARBINARY(5) = 
      CONVERT(VARBINARY(5), CHAR(157)+CHAR(144)+CHAR(143)+CHAR(141)+CHAR(129))

DECLARE 
    @separator_vc  VARCHAR(10) = CONVERT(VARCHAR(10), @separator_bin, 2)
    ,@NULL_repl_vc VARCHAR(10) = CONVERT(VARCHAR(10), @NULL_repl_bin, 2)

;WITH cte AS
(
    SELECT
        id, DESCRIPTION, number,
        word1, word2, word3,
        COMBINED_VALUE, 
        valid_f, word1_binary
    FROM 
        #t
    OUTER APPLY
        (SELECT 
             number_binary = CONVERT(VARBINARY(8000), number)
             ,word1_binary = CONVERT(VARBINARY(8000), word1 )
             ,word2_binary = CONVERT(VARBINARY(8000), word2 )
             ,word3_binary = CONVERT(VARBINARY(8000), word3 )
        )binaries
    OUTER APPLY
        (SELECT valid_f = IIF(COUNT(*) = 0, 1, 0)
         FROM (VALUES  (CONVERT(VARCHAR(8000), number_binary, 2))
                      ,(CONVERT(VARCHAR(8000), word1_binary , 2))
                      ,(CONVERT(VARCHAR(8000), word2_binary , 2))
                      ,(CONVERT(VARCHAR(8000), word3_binary , 2)) )v(v)
         JOIN (VALUES  (@separator_vc)
                      ,(@NULL_repl_vc) )w(w) 
           ON CHARINDEX(w, v COLLATE Latin1_General_BIN2) > 0
        )validator
    OUTER APPLY
        (SELECT COMBINED_VALUE = 
                            COALESCE(number_binary, @NULL_repl_bin) 
         + @separator_bin + COALESCE(word1_binary , @NULL_repl_bin) 
         + @separator_bin + COALESCE(word2_binary , @NULL_repl_bin) 
         + @separator_bin + COALESCE(word3_binary , @NULL_repl_bin) 
        )combiner
    )
    SELECT
        id
        ,DESCRIPTION
        ,number
        ,word1_encapsulated = '>' + word1 + '<'
        ,word2_encapsulated = '>' + word2 + '<'
        ,word3_encapsulated = '>' + word3 + '<'
        ,COMBINED_VALUE
        ,valid_f
        ,repeats
        ,COMBINED_VALUE_dlen = DATALENGTH(COMBINED_VALUE)
        ,word1_varchar       = CONVERT(VARCHAR(8000), word1_binary, 2)
        ,separator_varchar   = @separator_varchar
    FROM 
        cte o
    OUTER APPLY
        (SELECT repeats = COUNT(*) 
         FROM cte i
         WHERE o.COMBINED_VALUE = i.COMBINED_VALUE
        )repeatfinder
    ORDER BY 
        id