Функция Binary_Checksum и HashBytes

Question

У меня сложный запрос, который использует много функций двоичной контрольной суммы, когда я тестировал его с некоторыми тестовыми данными для двух разных записей, он фактически возвращал мне одно и то же значение контрольной суммы. Пожалуйста, найдите тестовые данные, которые я использовал ниже

SELECT BINARY_CHECKSUM(16   ,'EP30461105',1) AS BinaryCheckSumEx UNION ALL
SELECT BINARY_CHECKSUM(21   ,'EP30461155',1) AS BinaryCheckSumEx

Сейчас я пытаюсь использовать функцию HASHBYTES с алгоритмом 'MD5', для которой я могу быть уверен, что получу уникальные записи, но сейчас меня беспокоит то, что в текущем запросе я использую значение 'Checksum', чтобы присоединиться к моим операторам 'Merge' искать новые записи. Так как "HashBytes" возвращает мне тип данных Varbinary, какую нагрузку на производительность я могу ожидать, если заменить условия соединения полем "HashByte".

SELECT HASHBYTES('MD5', CONCAT(Col1,Col2,Col3,Col4,..))

Более того, мне нужно создать хеширование для нескольких столбцов, и в этом случае мне понадобится дополнительная функция Concat, что приведет к дополнительным нагрузкам на мою производительность.

Answer 1

Вот варианты:

1. Использование индекса по хешу как VARBINARY

   • Это не очень хорошая идея. проверьте эти ссылки:
   • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35b61bb0-1fa8-4a2f-a9fb-729a1874dcf8/clustered-index-on-a-varbinary-column?forum=sqldatabaseengine
   • Индексирование SQL Server - varchar(100) против varbinary (100)? [конвертировать данные]

2. Использовать BINARY_CHECKSUM

   • Это хорошо, но проблема в том, что есть большая вероятность дублирования в контрольной сумме, и когда вы гугляете, вы видите, что у многих людей есть проблемы с ней.

Однако есть небольшая вероятность того, что контрольная сумма не изменится. По этой причине мы не рекомендуем использовать CHECKSUM для определения того, изменились ли значения, если только ваше приложение не может допустить случайного пропуска изменения. Попробуйте вместо этого использовать HashBytes. Если указан алгоритм хеширования MD5, вероятность того, что HashBytes вернет один и тот же результат для двух разных входных данных, будет значительно ниже, чем CHECKSUM.

Источник: https://msdn.microsoft.com/en-us/library/ms189788(v=SQL.100).aspx

3. Приведение HASBYTES к BIGINT и наличие индекса
   • Это не очень хорошая идея

Я также хотел бы быть осторожным при преобразовании хешированного значения в BIGINT, учитывая, что BIGINT составляет всего 8 байтов, но все алгоритмы хеширования - даже MD5 - больше 8 байтов (MD5 = 16 байтов, SHA1 = 20, SHA2_256 = 32 и SHA2_512 = 64) А преобразование двоичных значений размером более 8 байтов в BIGINT молча усекает значения. Следовательно, вы теряете точность и увеличиваете количество ложных срабатываний. Следующий запрос показывает это поведение:

SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFF),      --  7 bytes = 72057594037927935
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF),    --  8 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFF),  --  9 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF) -- 10 bytes = -1

Источник: https://dba.stackexchange.com/questions/154945/index-maintenance-for-varbinary

4. Приведение HASHBYTES к VARCHAR и наличие индекса
   • Это хороший выбор
   • У вас есть два варианта:

а) Если вы используете SQL 2008 или выше

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', CONTENT),2)

б) Если вы используете SQL 2005

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', CONTENT)), 3, 32)

PS: Если вам интересно, какой алгоритм хеширования следует использовать:

MD5 = 16 bytes
SHA1 = 20 bytes
SHA2_256 = 32 bytes
SHA2_512 = 64 bytes

Источник: https://blogs.msdn.microsoft.com/sqlsecurity/2011/08/26/data-hashing-in-sql-server/

Для вашего второго вопроса вы должны сделать Hash столбцы PERSISTED, чтобы избежать влияния на выполнение каждого запроса.