Usage of arithabort numeric_truncation set option practicals

We can actually ignore the scale truncation errors via 'set arithabort numeric_truncation off' command.
This also gives a better picture as why the errors were thrown and actually shows us that how many scale values we lost when implicit conversions were made ( good debugging )
Also even if the truncation error occurs, the batch sql continues to execute so might be that the job which executed the procedure was not failing but the data where this truncation is happening, was not getting displayed.
Here are some examples and comments:
Case 1 :
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
Truncation error occurred.
Command has been aborted.
1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,13),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
(1 row affected)
1> set arithabort numeric_truncation on ( this is ON by default thus we get errors in scale losses )
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(28,18),1.4556537149915)
2>
140416.4132553443690115172833604056875 -- for original setting, we were loosing out 5 scale values, hence when we made the scale as 18, no truncation occurred.
Case 2
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
Truncation error occurred.
Command has been aborted.

1> set arithabort numeric_truncation off
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.828113962515) / 100) * convert(numeric(35,20),1.45565371499151111111)
2>
--------------------------------------------------------------------------------
140416.41325534766300175115789757
Lets find out how much it goes..
1> select 10177.244681*(947.828113962515/100)*1.45565371499151111111
2>
---------------------------------------------------------------
140416.4132553476630017511578975709032198096365000000000
Its going beyond 38 which is the max scale we can have on numeric and hence truncating…


Case 3 :
In truncation errors being off :

1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,20),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.41325534436901151728336040
We know that this multiplication goes to 56875 as per Case 1 so adding 5 more to scale would lead to no scale loss

1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875

1> set arithabort numeric_truncation on
2>
1> select (convert(numeric(17,6),10177.244681) * convert(numeric(35,12),947.8281139625) / 100) * convert(numeric(35,25),1.4556537149915)
2>
--------------------------------------------------------------------------------
140416.4132553443690115172833604056875
We definetely do not need that length of precesions and can safely ignore the truncation errors..
Hope this helps.