Rounding Difficulties in MS SQL Server

Good day, habrovchane! I had to face in the project with the accuracy of calculations in MS SQL Server and I discovered not quite intuitive behavior when performing seemingly intuitive operations.

To seed the question (try to answer it without performing):
What will be the result of the operation?

declare @var1 decimal(38,10) = 0.0000007, @var2 decimal(38,10) = 1; select @var1 * @var2; 

Answer and explanation under the cut


So, first answer: 0.000001
')

Which one

In fact, the answer is quite simple, but this is no easier. The thing is that when performing arithmetic operations with decimal numbers, the result can be much more than the original values, for example, if we multiply 10 ^ 6 and 10 ^ 6, we get 10 ^ 12. This is already 6 digits more than the original values. Similarly with division. Therefore, MS SQL when calculating the type of the result expression applies the following rules :

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)

* The precision and scale of the result have an absolute maximum of 38. If the value of precision exceeds 38, then the corresponding scale is reduced to prevent the integral part of the result from truncating.

The documentation does not contain a detailed description of how rounding occurs and to what extent, but experimentally I could not achieve rounding more than decimal (38.6).

Hence the result of the expression at the beginning: 0.000001 exactly 6 decimal places. Not to be unfounded, run the following query:

 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; 

We get the following result:

res	Basetype	Precision	Scale
0.000001	decimal	38	6

How to live with it?

You will have to come to terms with this and always (absolutely always!) To very accurately set accuracy. In our case, this script will return the expected result:

 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

Instead of an afterword

And lastly some more sql-magic. What will happen as a result of the execution of such a script:

 declare @var1 decimal(38,10) = 0.0000007, @var2 int = 1; select @var1 * @var2, @var1 * 1; 

PS: you also need to be careful with the aggregation operations, because they also change the accuracy of the result. Here is a good article describing the problem.