Einführung

In modernen Data-Warehouse- und ETL-Szenarien ist es unerlässlich, Datenänderungen schnell und zuverlässig zu erkennen. Hash-basierte Verfahren wie CHECKSUM, BINARY_CHECKSUM oder die kryptografischen Funktionen HASHBYTES (mit Algorithmen wie SHA2-256, SHA2-512 oder MD5) bieten hierfür unterschiedliche Vor- und Nachteile. Doch welches Verfahren liefert in Ihrer Umgebung die beste Performance? Das vorgestellte T-SQL-Skript automatisiert den Vergleich all dieser Methoden über zehn aufeinanderfolgende Durchläufe und ermittelt sowohl Laufzeiten pro Methode und Durchgang als auch die durchschnittliche Ausführungsdauer.

Warum Performance-Messung wichtig ist

  • Rohdaten-Volumen: Bei Millionen oder gar Milliarden von Zeilen können bereits wenige zusätzliche Millisekunden pro Zeile Stunden an Laufzeit bedeuten.
  • Index-Strategien: Einige Hash-Verfahren lassen sich indizieren (z. B. CHECKSUM), andere weniger gut.
  • Kollisionsrisiko vs. Genauigkeit: Einfache Checksummen sind schneller, weisen jedoch höhere Kollisionswahrscheinlichkeiten auf. Kryptografische Hashes sind präziser, aber rechenintensiver.

Mit gezielten Performance-Messungen gewinnen Sie belastbare Kennzahlen, um in Ihrer Umgebung eine fundierte Entscheidung zu treffen.

Technische Funktionsweise im Detail

1. CHECKSUM

  • Zweck: Erzeugt einen 32-Bit-Integer-Hash über eine Liste von Ausdrücken, hauptsächlich für Hash-Indizes und Change Detection [Stack Overflow | Microsoft Learn].
  • Algorithmus: Intern basiert CHECKSUM auf einer Folge von 4-Bit-Linksrotationen und XOR-Operationen über die Binärdarstellungen der Argumentwerte, wodurch ein einfacher, schneller—but stark kollisionsanfälliger—Hash entsteht [Stack Overflow].
  • Kollationsabhängigkeit: Die Sortiergewichte einer Kollation fließen in die Bitmuster der Zeichen ein, sodass derselbe Text in verschiedenen Kollationen unterschiedliche Prüfsummen ergibt [Microsoft Learn].
  • Einschränkungen:
    • Hohe Kollisionwahrscheinlichkeit (32-Bit Länge).
    • Entfernt bei nchar/nvarchar führende und nachgestellte Leerzeichen.
    • Reihenfolge der Spalten beeinflusst das Ergebnis.

2. BINARY_CHECKSUM

  • Zweck: Liefert einen kollationsunabhängigen Integer-Hash, berechnet über die rohen Byte-Repräsentationen der Daten [Microsoft Learn].
  • Algorithmus:
    • Microsoft dokumentiert das genaue Verfahren nicht öffentlich.
    • Erfahrungsberichten zufolge werden die Bytes jeder Spalte in der Sequenz der Tabellendefinition per XOR-Art zusammengeführt (ähnlich wie bei Checksum-Aggregaten) [SQLServerCentral].
  • Besonderheiten:
    • Ignoriert nicht-vergleichbare Datentypen (text, ntext, image, xml, CLR-Typen).
    • Ist kollationsunabhängig, da keine Sortiergewichte angewendet werden.
    • Auch hier kann ein gelegentliches „Übersehen“ von Änderungen nicht ganz ausgeschlossen werden.

3. HASHBYTES

  • Zweck: Kryptografisch sichere Hash-Funktion mit variabler Länge (abhängig vom Algorithmus), geeignet für Change-Detection mit sehr geringer Kollisionswahrscheinlichkeit [Microsoft Learn].
  • Unterstützte Algorithmen:
    • MD5 (128 Bit) [Wikipedia]
    • SHA-1 (160 Bit)
    • SHA2-256 (256 Bit)
    • SHA2-512 (512 Bit) [SQL Shack]
  • Interne Funktionsweise:
    • MD5 verwendet das Merkle–Damgård-Konstrukt mit 512-Bit-Blöcken, vier Operationsrunden à je 16 Schritten, bestehend aus modularer Addition, bitweisen Nicht-/UND-/ODER-/XOR-Operationen und 32-Bit-Linksrotationen [Wikipedia].
    • SHA-2 ist ebenfalls Merkle–Damgård-basiert, verarbeitet 512-Bit-Blöcke (SHA2-256: 64 Runden) bzw. 1024-Bit-Blöcke (SHA2-512: 80 Runden) mit einer Mischung aus modularer Addition, bitweisen Rotationen und logischen Funktionen [MSSQLTips].
  • Praxistipps:
    • Input-Limit: Bis zu 8 000 Bytes Eingabe, darüber hinausige Daten müssen segmentweise gehasht oder per Hash-Aggregation kombiniert werden [Database Administrators Stack Exchange].
    • Mehrspaltiger Hash: Kombinieren Sie mehrere Spalten mit CONCAT_WS('|',…), wandeln Sie Datums-/Zahlentypen vorher gezielt in Strings um, um Formatierungsvarianten zu vermeiden [Stack Overflow].
    • Output-Typ: varbinary, meist 16 Bytes (MD5), 32 Bytes (SHA2-256) oder 64 Bytes (SHA2-512).

Das Skript im Überblick

Schritt für Schritt Anleitung

  • Vorbereitung
    • Löschen bisheriger Temp-Tabellen aus tempdb.
    • Anlegen der Performance-Tabelle #Perf mit den Spalten Method (Hash-Verfahren), Run (Durchlauf-Nummer) und DurationMs (Dauer in Millisekunden).
IF OBJECT_ID('tempdb..#Perf') IS NOT NULL DROP TABLE #Perf;
CREATE TABLE #Perf (
    Method      NVARCHAR(50),
    [Run]       INT,
    DurationMs  BIGINT
);
  • Zehnfache Durchführung
    • In einer WHILE-Schleife von 1 bis 10 werden nacheinander die folgenden Methoden ausgeführt:
      • Default CHECKSUM
      • CHECKSUM mit expliziter Windows-Kollation
      • BINARY_CHECKSUM
      • HASHBYTES (SHA2_256, SHA2_512, MD5)
    • Jede Methode schreibt per SELECT … INTO #Temp_* eine Tabelle mit allen Original-Spalten zuzüglich der berechneten Hash-/Checksum-Spalte.
    • Mittels SYSUTCDATETIME() und DATEDIFF(millisecond,…) wird die benötigte Zeit gemessen und in #Perf protokolliert.
    • Die jeweilige #Temp_*-Tabelle wird sofort gelöscht, um Speicher zu sparen und Ergebnis-Ausgaben zu verhindern.
DECLARE @run INT = 1;
WHILE @run <= 10
BEGIN
  -- Jede Hash-Methode hier:
  -- 1) SYSUTCDATETIME() vor Ausführung setzen
  -- 2) SELECT … INTO #Temp_<Methode> FROM … mit Hash-Spalte
  -- 3) SYSUTCDATETIME() nach Ausführung setzen
  -- 4) INSERT INTO #Perf mit DATEDIFF
  -- 5) DROP TABLE #Temp_<Methode>
  SET @run += 1;
END
-- Default CHECKSUM
SET @t1 = SYSUTCDATETIME();

SELECT …,
       CHECKSUM(…Spalten…) AS Checksum_Default
INTO #Temp_Default
FROM lw.archive_order_Status_NEW;

SET @t2 = SYSUTCDATETIME();

INSERT INTO #Perf (Method, [Run], DurationMs)
VALUES ('Default CHECKSUM', @run, DATEDIFF(ms, @t1, @t2));

DROP TABLE #Temp_Default;

CHECKSUM erzeugt einen 32-Bit-Hash über eine Liste von Ausdrücken und ist kollationsabhängig.

SELECT …,
       BINARY_CHECKSUM(…Spalten…) AS Checksum_Binary
INTO #Temp_Binary
FROM …

Unterschied zu CHECKSUM: Arbeitet auf den reinen Byte-Darstellungen der Daten und ist kollationsunabhängig

SELECT …,
       HASHBYTES(
         'SHA2_256',
         CONCAT_WS('|', …alle Spalten als Strings…)
       ) AS Hash_SHA2_256
INTO #Temp_Hash256
FROM …

Warum CONCAT_WS? Um alle Spalten zu einem einzigen varbinary-Input für HASHBYTES zu verbinden (ohne automatischen Datums-/Zahlen-Formatierungsfehler)

  • Auswertung
    • Abschließend werden alle Einzelmessungen aufgeführt und die durchschnittliche Laufzeit pro Methode berechnet.

Auswertung der Ergebnisse

Am Ende des Skripts stehen zwei Abfragen:

  • Einzelergebnisse pro Methode und Run
    SELECT Method, [Run], DurationMs AS Dauer_ms
FROM #Perf
ORDER BY Method, [Run];
    • Durchschnittliche Laufzeit je Methode
    SELECT Method, AVG(DurationMs) AS AvgDurationMs
FROM #Perf
GROUP BY Method
ORDER BY AvgDurationMs;

      Damit erhalten Sie:

      • Varianz innerhalb der zehn Läufe, um die Stabilität einer Methode zu beurteilen.
      • Mittelwert, um die schnellste Methode in Ihrer Umgebung zu identifizieren.

      Anpassungsmöglichkeiten

      • Anzahl der Läufe: Passen Sie @run <= 10 je nach gewünschter Granularität an.
      • Weitere Algorithmen: Fügen Sie z. B. SHA1 oder andere Hashes hinzu, indem Sie eine zusätzliche #Temp_*-Phase ergänzen.
      • Kollationsvarianten: Testen Sie zusätzliche Kollationen, indem Sie weitere CHECKSUM(… COLLATE …)-Blöcke einfügen.
      • Tabellen-Variable statt Temp-Tabelle: Für kleinere Datenmengen kann DECLARE @Perf TABLE (…) performant sein.

      Meine Ergebnisse

      Bei meiner Tabelle mit ca 45 Millionen Zeilen sind das meine Ergebnisse:

      Fazit zum Skript

      Mit diesem T-SQL-Skript erhalten Sie in wenigen Minuten einen belastbaren Performance-Vergleich gängiger Prüf- und Hashverfahren in SQL Server. Auf Basis der gewonnenen Durchschnittswerte können Sie entscheiden, ob die native CHECKSUM-Funktion ausreicht oder ob Sie auf präzisere, wenn auch rechenintensivere Ansätze wie HASHBYTES (z. B. SHA2_512 oder MD5) umschwenken sollten.

      Fazit

      MethodePerformanceCollation-sicherKollisionssicher
      Default CHECKSUMSehr schnellNeinNiedrig
      CHECKSUM mit expliziter KollationSchnellNeinNiedrig
      BINARY_CHECKSUMSehr schnellJaNiedrig
      HASHBYTES (MD5)ModeratJaMittel
      HASHBYTES (SHA2-256)LangsamJaHoch
      HASHBYTES (SHA2-512)Sehr langsamJaSehr hoch
      • CHECKSUM ist sehr schnell, aber kann durch Kollationsunterschiede abweichen und neigt zu Kollisionen.
      • BINARY_CHECKSUM ist kollationsunabhängig und ebenfalls performant, jedoch nicht komplett collision-sicher.
      • HASHBYTES (MD5, SHA2-256, SHA2-512) bietet die höchste Zuverlässigkeit bei Change-Detection auf Kosten höherer Rechenzeit und einer Limitierung der Eingabelänge.

      Nutzen Sie die gewonnenen Durchschnittswerte als Entscheidungsgrundlage: Wenn Sie maximale Geschwindigkeit brauchen und gelegentliche Kollisionen tolerieren können, ist CHECKSUM oder BINARY_CHECKSUM ausreichend. Für kritische Szenarien mit minimalem Kollisionsrisiko greifen Sie zu HASHBYTES mit SHA2-256/512.

      Quellen

      Related posts:

      Default ThumbnailWenn eine Datenbank lügt: Wie Ihre Kollation Ihre Checksum trügt! CHECKSUMMEN (HASH) BERECHNUNG IN MS SQL Default ThumbnailInterne Abarbeitung von Joins HORIZONTALE UND VERTIKALE PARTITIONIERUNG VON TABELLEN