Decimal в SQL Server: точность вычислений и округление

Столбец decimal не делает автоматически точными все вычисления, из которых получается его значение. SQL Server определяет тип промежуточного результата по типам операндов. Дробная часть может исчезнуть ещё до записи, а агрегат способен переполниться, даже если целевой столбец достаточно велик. В расчётах платежей такие ошибки особенно неприятны: полученные суммы часто выглядят вполне правдоподобно.

Начните с бизнес-величины. Денежные суммы, курсы валют, проценты и количество требуют разных диапазонов. В decimal(p,s) параметр p задаёт общее число цифр, включая s цифр после запятой. Для decimal(12,2) остаётся десять цифр целой части. Выбор только количества десятичных знаков не определяет достаточный диапазон.

Тип важен до выполнения операции

Следующие выражения показывают, почему преобразование готового результата иногда запаздывает.

SELECT 5 / 2 AS IntegerResult,
       CAST(5 AS decimal(12,4)) / 2 AS DecimalResult,
       CAST(5 / 2 AS decimal(12,4)) AS CastTooLate;

Целочисленное деление возвращает 2. Если сначала преобразовать операнд, сохраняется результат 2,5. Преобразование уже вычисленного целого даёт 2,0000 и не восстанавливает потерянную половину.

Аналогично работает умножение. Произведение двух int может переполниться до внешнего преобразования в bigint. Расширяйте один операнд перед вычислением. Тип столбца, в который позже попадёт результат, не меняет правила выполнения исходного выражения.

У выражений decimal точность и масштаб также вычисляются по правилам. Для умножения исходная точность равна p1 + p2 + 1, а масштабы складываются. Максимальная точность ограничена 38. Дополнительные правила могут уменьшить масштаб, причём возможность переполнения остаётся. Деление иногда значительно увеличивает необходимое число дробных знаков. Поэтому повсеместный decimal(38,...) не гарантирует неограниченную точность.

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

Место округления определяет сумму

Две разумные политики выставления счёта могут давать разные итоги.

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;

Округление каждой строки 19,995 до двух знаков с последующим суммированием даёт 40,000. Суммирование до округления даёт 39,990. Последний ноль отражает сохранённый масштаб выражения; содержательная разница составляет одну сотую денежной единицы.

Расположение ROUND не должно случайно определять финансовые правила. Согласуйте, рассчитываются ли налог и скидки по строкам или по всему счёту, в каком порядке и куда относится остаток. ROUND в SQL Server разрешает точное попадание посередине округлением от нуля. Другая система может использовать иной способ и получать другие суммы при одинаковых входных данных.

Не заменяйте decimal на float ради устранения переполнения. float использует приближённое двоичное представление и плохо подходит для требования точного десятичного совпадения. Для научных измерений приближённый тип может быть обоснован. Выбор зависит от смысла данных.

Расширяйте вход агрегата

SUM от выражения int возвращает int, даже если результат затем присваивается bigint.

DECLARE @Counts table (Quantity int);
INSERT @Counts VALUES (2000000000), (2000000000);
SELECT SUM(CONVERT(bigint, Quantity)) AS TotalQuantity
FROM @Counts;

Здесь каждое входное значение расширяется перед суммированием. Результат равен 4 000 000 000. Преобразование снаружи SUM не предотвратило бы внутреннее переполнение. Для decimal агрегат возвращает decimal(38,s), но доступная целая часть всё равно конечна.

Определите обработку отсутствующих данных. SUM пропускает NULL и возвращает NULL, если ненулевых в этом смысле входов нет. Замена такого результата числовым нулём иногда полезна для отчёта, однако может скрыть неизвестные финансовые значения. Неизвестная цена не означает бесплатный товар.

Включайте в проверки дробное деление, максимальные величины, отрицательные возвраты, точные середины округления и пустые наборы. Сопоставляйте результаты с вычислениями приложения и проверяйте каждый промежуточный шаг. Дополнительно проверяйте согласованность правил при повторном расчёте старого счёта: изменение формулы не должно незаметно менять уже согласованные суммы. Форматированная строка способна скрыть потерю точности, но не исправить её.

Техническая документация: Microsoft Learn: Precision, scale, and length · Microsoft Learn: SUM.