CHECKSUM - ciekawostka

Ciekawe zachowanie się funkcji CHECKSUM pokazał mi wczoraj jeden ze znajomych. Otóż CHECKSUM(10.00) = CHECKSUM(100.00). Zaobserwował On taki zachowanie gdy sprawdzał jakie rekordy zmieniły się w tabelce, a zmiana polegała na zwiększeniu ceny 10-krotnie. Korzystając z funkcji CHECKSUM okazało się, że nic się nie zmieniło. Przykład poniżej :

DECLARE @T1 TABLE
(
    ID INT,
    NAZWA VARCHAR(50),
    CENA DECIMAL(10,2)
);

DECLARE @T2 TABLE
(
    ID INT,
    NAZWA VARCHAR(50),
    CENA DECIMAL(10,2)
);

INSERT INTO @T1 (ID, NAZWA, CENA)
VALUES (1, 'TEST1', 125.23)
    , (2, 'TEST2', 10.00)
    , (3, 'TEST3', 23.23);

INSERT INTO @T2 (ID, NAZWA, CENA)
VALUES (1, 'TEST1', 125.23)
    , (2, 'TEST2', 10.00)
    , (3, 'TEST3', 23.23);

-- DANE W OBU TABELKACH SĄ IDENTYCZNE
SELECT * FROM @T1
SELECT * FROM @T2

-- SPRAWDZENIE CZY SĄ JAKIEŚ REKORDY, KTÓRE SIĘ RÓŻNIĄ
SELECT *
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID
WHERE CHECKSUM(T1.NAZWA, T1.CENA) <> CHECKSUM(T2.NAZWA, T2.CENA)

-- ZWIĘKSZENIE CENY W TABELI @T1
UPDATE @T1
SET CENA = CENA * 10

-- DANE W TABELACH RÓŻNIĄ SIĘ
SELECT * FROM @T1
SELECT * FROM @T2

-- SPRAWDZENIE CZY REKORDY RÓŻNIĄ SIĘ ZA POMOCĄ CHECKSUM DAJE WYNIK NEGATYWNY
-- WEDŁUG CHECKSUM NIC SIĘ NIE ZMIENIŁO
SELECT *
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID
WHERE CHECKSUM(T1.NAZWA, T1.CENA) <> CHECKSUM(T2.NAZWA, T2.CENA)

-- OBIE SUMY KONTROLNE SĄ TAKIE SAME
SELECT CHECKSUM(T1.NAZWA, T1.CENA) AS CHECKSUM_T1
    , CHECKSUM(T2.NAZWA, T2.CENA) AS CHECKSUM_T2
FROM @T1 AS T1
    INNER JOIN @T2 AS T2 ON T1.ID = T2.ID

W helpie do funkcji CHECKSUM jest fajne stwierdzenie, które może tłumaczyć takie zachowanie :

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

Jak poczytałem na necie to problem jest znany. Jedni piszą swoje funkcje sprawdzające sumę kontrolną np. w C# i podłączają je jako CLR. Inni korzystają z funkcji  HashBytes, ale tu elementem wejściowym musi być tekst.
Do powyższej sytuacji rozwiązaniem wydaje się "skastowanie" wartości numerycznej na VARCHARa. Wówczas osiągamy to o co nam chodziło.

A może jest inny sposób ...?

Azure UNICODE Friendly

Jeszcze jedna ciekawostka ze świata Azure jest taka, że jest on bardzo UNICODE. Jeżeli w tabelce mamy pole typu VARCHAR, a w nim np. słówko 'PAWEŁ' to szukając prostym zapytaniem :

SELECT *
FROM TABELKA
WHERE NAZWA = 'PAWEŁ';

... otrzymamy zero wyników. Azure sprytnie zamienia sobie "Ł" na "L" i szuka 'PAWEL' i oczywiście nie znajduje. Rozwiązania na znalezienie interesujących nas rekordów są dwa : zmiana pola z VARCHAR na NVARCHAR lub napisanie zapytania w taki sposób :

SELECT *
FROM TABELKA
WHERE NAZWA = N'PAWEŁ';

Azure

Ostatnio miałem okazję przyglądać się pracom przenoszenia aplikacji do chmury. Ciekawe był sposób sprawdzania poprawności przenoszonego kodu SQL. Jednej z procedur serwerowych nie można było przenieść do bazy w chmurze. Pojawiał się błąd (dokładnie nie pamiętam angielskiej wersji), że tworzenie globalnej tabeli tymczasowej nie jest dozwolone. Zajrzeliśmy do wnętrza procedury, ale nie znaleźliśmy kodu odpowiedzialnego za tworzenie globalnej tabeli tymczasowej. Była tam deklaracja lokalnej tabeli. Myśleliśmy, że to w tym problem. Zmieniliśmy kod tworzenia tabeli tymczasowej na tworzenie zmiennej tabelarycznej. Niestety nie pomogło.

Po kolejnym okrążeniu kodu procedury okazało się, że w jednym z zapytań (zwykłe polecenie SELECT) w klauzuli WHERE było coś takiego :

WHERE NAZWA LIKE 'XYZ ##%##%'

Zakomentowaliśmy tą linijkę kodu i proces zakładania procedury przeszedł bez błędów.

Z tego wynika, że tak naprawdę nie było szukane tworzenie globalnej tabeli tymczasowej, a dwóch #. Okazuje się, że dwa # są zawsze oznaczenie globalnej tabeli tymczasowej ;).

Co Wy na to, bo dla mnie taka walidacja kodu to trochę nie fajnie?