Dezimalrechnung in SQL Server: Präzision vor dem Runden
Vermeiden Sie Ganzzahldivision, Zwischenüberläufe und abweichende Rechnungssummen durch passende Datentypen und ausdrücklich festgelegte Rundungsregeln.
Eine decimal-Spalte garantiert nicht, dass jede vorgelagerte Berechnung exakt ist. SQL Server bestimmt Zwischenergebnisse aus den Datentypen der Operanden. Informationen können bereits vor der abschließenden Zuweisung verloren gehen. Auch ein Aggregat kann überlaufen, obwohl die Zielspalte groß genug wäre. In Abrechnungssystemen bleiben solche Fehler häufig lange unentdeckt, weil die Ergebnisse plausibel aussehen.
Beginnen Sie mit der fachlichen Größe. Geldbeträge, Wechselkurse, Prozentsätze und Stückzahlen benötigen unterschiedliche Wertebereiche. Bei decimal(p,s) bezeichnet p die gesamte Stellenzahl und s die Nachkommastellen. decimal(12,2) lässt somit zehn Stellen vor dem Komma zu. Nur die Nachkommastellen festzulegen reicht für einen belastbaren Entwurf nicht aus.
Den Rechenweg passend typisieren
Diese drei Ausdrücke zeigen den Unterschied zwischen einer frühen und einer verspäteten Konvertierung.
SELECT 5 / 2 AS IntegerResult,
CAST(5 AS decimal(12,4)) / 2 AS DecimalResult,
CAST(5 / 2 AS decimal(12,4)) AS CastTooLate;
Die Ganzzahldivision liefert 2. Wird zuerst ein Operand konvertiert, bleibt das Ergebnis 2,5 erhalten. Die nachträgliche Konvertierung ergibt dagegen 2,0000 und kann den verlorenen Bruchteil nicht rekonstruieren.
Entsprechendes gilt für Multiplikationen. Zwei int-Werte können schon bei der Multiplikation überlaufen, bevor eine äußere Umwandlung nach bigint greift. Konvertieren Sie einen Operanden vor der Operation. Der Typ einer später beschriebenen Zielspalte verändert die Auswertung des ursprünglichen Ausdrucks nicht.
Auch decimal-Ausdrücke erhalten berechnete Präzision und Skala. Für eine Multiplikation ergibt sich zunächst p1 + p2 + 1 als Präzision und s1 + s2 als Skala. Die maximale Präzision ist 38. Weitere Regeln können die Skala reduzieren oder trotzdem einen Überlauf zulassen. Divisionen können besonders viele Nachkommastellen erzeugen. Pauschales Konvertieren nach decimal(38,...) löst deshalb nicht jedes Problem.
Planen Sie anhand maximaler Mengen, Preise und Kurse sowie der zulässigen Rundungsabweichung. Der Zwischentyp muss das größte Produkt aufnehmen können. Legen Sie auch Präzision und Skala von Anwendungsparametern bewusst fest, sofern der Treiber diese Eigenschaften anbietet.
Den Rundungszeitpunkt fachlich entscheiden
Zwei nachvollziehbare Rechnungsverfahren können verschiedene Summen liefern.
DECLARE @Lines table (Amount decimal(10,3));
INSERT @Lines VALUES (19.995), (19.995);
SELECT SUM(ROUND(Amount, 2)) AS RoundedPerLine,
ROUND(SUM(Amount), 2) AS RoundedInvoice
FROM @Lines;
Werden beide Positionen mit 19,995 zuerst einzeln auf zwei Stellen gerundet, beträgt die Summe 40,000. Werden sie zuerst summiert, ergibt die Rundung 39,990. Die letzte angezeigte Null stammt aus der erhaltenen Skala; fachlich unterscheiden sich die Ergebnisse um einen Cent.
Die Position von ROUND darf diese Entscheidung nicht zufällig treffen. Klären Sie, ob Steuern und Rabatte pro Position oder pro Rechnung berechnet werden, in welcher Reihenfolge und wie Restbeträge verteilt werden. SQL Server rundet bei genau halbem Abstand von null weg. Ein anderes System kann eine andere Regel verwenden und dadurch trotz identischer Eingaben abweichen.
float ist kein geeigneter Ersatz, nur um einen Überlauf zu umgehen. Der Typ verwendet eine näherungsweise binäre Darstellung. Bei verbindlich exakten Dezimalbeträgen ist das problematisch; bei naturwissenschaftlichen Messwerten kann es dagegen angemessen sein. Entscheidend ist die fachliche Vereinbarung.
Schon das Aggregat richtig berechnen
SUM über int liefert int. Eine nachträgliche Zuweisung an bigint vergrößert den internen Aggregattyp nicht.
DECLARE @Counts table (Quantity int);
INSERT @Counts VALUES (2000000000), (2000000000);
SELECT SUM(CONVERT(bigint, Quantity)) AS TotalQuantity
FROM @Counts;
Hier wird der Eingabeausdruck vor SUM erweitert. Das Ergebnis beträgt 4.000.000.000. Eine Konvertierung erst außerhalb von SUM würde den ursprünglichen Überlauf nicht verhindern. SUM über decimal liefert decimal(38,s), besitzt aber weiterhin einen endlichen Bereich für die Vorkommastellen.
Definieren Sie auch die Bedeutung fehlender Werte. SUM ignoriert NULL und liefert NULL, wenn kein einziger nichtleerer Eingabewert existiert. Daraus pauschal null im numerischen Sinn zu machen kann fehlende Finanzdaten verschleiern. Ein unbekannter Preis ist kein kostenloser Artikel.
Sinnvolle Prüffälle enthalten Bruchdivisionen, maximale Beträge, negative Erstattungen, Rundungsgrenzen und leere Eingaben. Vergleichen Sie die Zwischenwerte mit der Anwendungsberechnung. Eine formatierte Ausgabe kann Genauigkeitsverlust verbergen, aber nicht rückgängig machen.
Technische Referenzen: Microsoft Learn: Precision, scale, and length · Microsoft Learn: SUM.