Pull to refresh

Трудности округления в MS SQL Server

SQL *Microsoft SQL Server *
Доброго дня, хабровчане! Пришлось мне в проекте столкнуться с точностью вычислений в MS SQL Server и я обнаружил не совсем интуитивное поведение при выполнении казалось бы интуитивных операций.

Для затравки вопрос (попробуйте ответить на него, не выполняя):
Каков будет результат операции?
declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1;
select @var1 * @var2;

Ответ и объяснение под катом


Итак, сначала ответ: 0.000001

Какого?

На самом деле ответ достаточно прост, но от этого не легче. Все дело в том, что при выполнении арифметических операций с десятичными числами результат может быть сильно больше исходных значений, например, если умножить 10^6 и 10^6, то получим 10^12. Это аж на 6 разрядов больше, чем исходные значения. Аналогично и с делением. Поэтому MS SQL при вычислении типа результирующего выражения применяет следующие правила:
Operation
Result precision
Result scale *
e1 + e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1, s2)
e1 — e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1, s2)
e1 * e2
p1 + p2 + 1
s1 + s2
e1 / e2
p1 — s1 + s2 + max(6, s1 + p2 + 1)
max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2
max(s1, s2) + max(p1-s1, p2-s2)
max(s1, s2)
e1 % e2
min(p1-s1, p2 -s2) + max( s1,s2 )
max(s1, s2)

* precision и scale результата имеют абсолютный максимум, равный 38. Если значение precision превышает 38, то соответствующий scale уменьшается, чтобы по возможности предотвратить усечение интегральной части результата.

В документации нет подробного описания как происходит округление и до каких пределов, но экспериментально у меня не получилось достичь округления больше, чем decimal(38,6).

Отсюда и результат выражения в начале: 0.000001 ровно 6 знаков после запятой. Чтобы не быть голословным, выполним следующий запрос:
declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1,
        @res sql_variant;
set @res = @var1 * @var2;
select @res, 
       SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
       SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
       SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;

Получим следующий результат:
res
BaseType
Precision
Scale
0.000001
decimal
38
6

Как же с этим жить?

Придется с этим смириться и всегда (абсолютно всегда!) очень деликатно выставлять точность. В нашем случае вот такой скрипт вернет ожидаемый результат:
declare @var1 decimal(18,10) = 0.0000007,
        @var2 decimal(18,10) = 1,
        @res sql_variant;
set @res = @var1 * @var2;
select @res, 
       SQL_VARIANT_PROPERTY(@res, 'BaseType') as BaseType, 
       SQL_VARIANT_PROPERTY(@res, 'Precision') as Precision,
       SQL_VARIANT_PROPERTY(@res, 'Scale') as Scale;

res
BaseType
Precision
Scale
0.00000070000000000000
decimal
37
20

Вместо послесловия

И на последок еще немного sql-магии. Что будет в результате выполнения вот такого скрипта:
declare @var1 decimal(38,10) = 0.0000007,
        @var2 int = 1;
select @var1 * @var2, @var1 * 1;

Ответ
@var1 * @var2 = 0.000001
@var1 * 1 = 0.00000070


PS: так же надо быть внимательным с операциями аггрегации, потому что они тоже меняют точность результата. Вот хорошая статья, описывающая проблему.
Tags:
Hubs:
Total votes 17: ↑14 and ↓3 +11
Views 32K
Comments Comments 11