BINARY CHECKSUM: примеры (SQL)

Примеры использования

Пример с одним выражением:

SELECT BINARY_CHECKSUM('Hello World') AS checksum_single;

checksum_single
101763762

Пример с несколькими аргументами:

SELECT BINARY_CHECKSUM('Hello', 'World', 2023) AS checksum_multi;

checksum_multi
-966477253

Использование с оператором * для всей строки таблицы:

CREATE TABLE #Test (id INT, name VARCHAR(10));
INSERT INTO #Test VALUES (1, 'John');
SELECT BINARY_CHECKSUM(*) AS row_checksum FROM #Test;
DROP TABLE #Test;

row_checksum
-1769839733

Сравнение контрольных сумм для выявления изменений в строке:

DECLARE @original_checksum INT = BINARY_CHECKSUM('Data');
DECLARE @new_checksum INT = BINARY_CHECKSUM('Data');
SELECT 
    CASE WHEN @original_checksum = @new_checksum 
    THEN 'Не изменилось' 
    ELSE 'Изменено' 
    END AS result;

result
Не изменилось

Аналоги в MS SQL Server

CHECKSUM: вычисляет значение на основе списка аргументов. Менее чувствительна к изменениям порядка столбцов по сравнению с BINARY_CHECKSUM. Может быть предпочтительнее для создания хэш-индексов.

CHECKSUM_AGG: агрегатная функция, возвращает контрольную сумму для набора значений. Используется для вычисления контрольной суммы по группе строк.

HASHBYTES: поддерживает различные алгоритмы хэширования (MD5, SHA1, SHA2_256). Предоставляет более криптографически стойкий результат, но работает медленнее. Используется, когда требуется минимизировать коллизии.

Выбор функции зависит от задачи. Для быстрого обнаружения изменений в строке подходит BINARY_CHECKSUM. Для построения хэш-индексов или вычисления контрольной суммы по группе строк используют CHECKSUM и CHECKSUM_AGG. Для задач, связанных с безопасностью или где критичны коллизии, применяют HASHBYTES.

Функции в других СУБД

MySQL: Функция CRC32() вычисляет циклическую избыточную сумму и возвращает 32-битное беззнаковое значение. Пример:

SELECT CRC32('Hello World');

CRC32('Hello World')
1243066710

Oracle: Специальной встроенной функции, аналогичной BINARY_CHECKSUM, нет. Обычно используют ORA_HASH или пользовательские функции. Пример:

SELECT ORA_HASH('Hello World') FROM dual;

ORA_HASH('HELLOWORLD')
302097288

PostgreSQL: Можно использовать функцию hashtext() или md5() (возвращает текст). Пример:

SELECT hashtext('Hello World');

hashtext
-327175028

SQLite: Встроенной функции нет, но можно использовать расширение или вычислять на стороне клиента.

Sybase ASE: Поддерживается функция checksum(), работающая аналогично.

Основное отличие функций в других СУБД от BINARY_CHECKSUM — различные алгоритмы вычисления, что приводит к разным результатам для одинаковых входных данных.

Распространённые ошибки

1. Непонимание коллизий. Разные данные могут давать одинаковый результат.

SELECT 
    BINARY_CHECKSUM('Checksum') AS c1,
    BINARY_CHECKSUM('checkSUM') AS c2;
-- Могут совпасть или различаться, но не гарантируется уникальность.

c1          c2
-1442956451 -1293301764

2. Использование для сравнения строк с разными типами данных. Результаты могут не соответствовать ожиданиям.

SELECT 
    BINARY_CHECKSUM(123) AS int_val,
    BINARY_CHECKSUM('123') AS str_val;

int_val     str_val
-1757653365 -335825447

3. Невосприимчивость к пробелам в конце строки в некоторых версиях или коллациях. Это может приводить к одинаковым контрольным суммам для строк, отличающихся только конечными пробелами.

SELECT 
    BINARY_CHECKSUM('text') AS no_space,
    BINARY_CHECKSUM('text ') AS with_space;
-- Могут быть равны при некоторых настройках.

no_space    with_space
-592374215  -592374215

4. Использование с NULL. Если все аргументы NULL, результат не NULL, а конкретное число.

SELECT BINARY_CHECKSUM(NULL) AS chk_null;

chk_null
0

Изменения в последних версиях

В документации Microsoft SQL Server не указано существенных изменений в поведении или синтаксисе функции BINARY CHECKSUM в последних основных версиях (2016, 2017, 2019, 2022). Алгоритм остаётся стабильным для обеспечения обратной совместимости.

Однако, при миграции между разными версиями или при изменении параметров сортировки (collation) сервера или базы данных, результаты функции могут незначительно отличаться, так как вычисления могут зависеть от внутренних представлений строк.

Рекомендуется не полагаться на неизменность значения контрольной суммы для одних и тех же данных при переносе между разными экземплярами или версиями SQL Server без предварительной проверки.

Расширенные примеры

Применение для обнаружения изменённых строк в таблице:

-- Создание временной таблицы с исходными данными и контрольной суммой
CREATE TABLE #Products (
    id INT,
    name VARCHAR(50),
    price MONEY,
    row_checksum AS BINARY_CHECKSUM(id, name, price) PERSISTED
);
INSERT INTO #Products (id, name, price) VALUES (1, 'Apple', 50.0);
-- Имитация обновления
UPDATE #Products SET price = 55.0 WHERE id = 1;
-- Проверка, изменилась ли контрольная сумма для строки
SELECT 
    *, 
    BINARY_CHECKSUM(id, name, price) AS new_checksum,
    CASE WHEN row_checksum <> BINARY_CHECKSUM(id, name, price) 
         THEN 'Changed' 
         ELSE 'Unchanged' 
    END AS status
FROM #Products;
DROP TABLE #Products;

id name   price row_checksum  new_checksum status
1  Apple  55.00 -1737313071   -1071799462  Changed

Использование в сочетании с GROUP BY для поиска потенциальных дубликатов по набору полей:

CREATE TABLE #Orders (OrderID INT, ClientID INT, Total INT);
INSERT INTO #Orders VALUES (1, 100, 500), (2, 100, 500), (3, 101, 300);
-- Поиск строк с одинаковыми ClientID и Total
SELECT ClientID, Total, COUNT(*) AS dup_count
FROM #Orders
GROUP BY BINARY_CHECKSUM(ClientID, Total), ClientID, Total
HAVING COUNT(*) > 1;
DROP TABLE #Orders;

ClientID Total dup_count
100      500   2

Влияние порядка аргументов на результат:

SELECT 
    BINARY_CHECKSUM('A', 'B', 1) AS order1,
    BINARY_CHECKSUM(1, 'A', 'B') AS order2;

order1      order2
-1570294095 -1291650299

Сравнение с вычислением по отдельным столбцам:

SELECT 
    BINARY_CHECKSUM('Test') AS whole,
    BINARY_CHECKSUM('T', 'e', 's', 't') AS split;

whole       split
125991246   125991246
-- В данном случае результаты совпали, но это не общее правило.