Avoid TSQL data conversion errors

Question

I think this is best asked as a simple example. The following SQL fragment causes the message "DB-Library Error: 20049 Severity: 4 Message: Data-conversion result overflow", but why?

declare @a numeric(18,6), @b numeric(18,6), @c numeric(18,6) select @a = 1.000000, @b = 1.000000, @c = 1.000000 select @a/(@b/@c) go 

How is this different from:

 select 1.000000/(1.000000/1.000000) go 

which works great?

Answer 1

I encountered the same problem the last time I tried to use Sybase (many years ago). Based on the mentality of SQL Server, I did not understand that Sybase would try to supplant decimals - which, in mathematics, is what it should do. :)

From the Sybase manual :

Arithmetic overflow errors occur when a new type has too few decimal places to post results.

And further down:

When implicitly converted to numeric or decimal types, loss of scale generates a scale error. Use the arithabort numeric_truncation parameter to determine how serious such an error is considered. The default value, arithabort numeric_truncation on, cancels the statement that causes errors, but continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, the Adaptive Server truncates the query results and continues processing.

Therefore, assuming that loss of accuracy is acceptable in your scenario, you will probably want the following at the beginning of your transaction:

 SET ARITHABORT NUMERIC_TRUNCATION OFF 

And then at the end of your transaction:

 SET ARITHABORT NUMERIC_TRUNCATION ON 

This is what allowed me this many years ago ...

Answer 2

This is just an assumption, but it may be that the DBMS does not look at the dynamic value of your variables, but only at potential values? Thus, a six-digit number divided by a six-digit number can lead to a ten-digit number; in the literal division of a DBMS knows that there is no overflow. Still not sure why the DBMS will take care, but should it not return the result of two six-digit divisions as up to an 18-decimal number?

Answer 3

Since you specified the variables in the first example, it is expected that the result will have the same declaration (i.e., numeric (18.6)), but it is not.

I have to say that the first worked in SQL2005, although (returned 1.000000 [the same declared type]), and the second returned (1.00000000000000000000000 [Altogether different declaration]).

Answer 4

Not directly related, but can save someone some time with arithmetic overflow errors using Sybase ASE (12.5.0.3).

I set a few default values ​​in a temporary table, which I was going to update later, and came across an arithmetic overflow error.

 declare @a numeric(6,3) select 0.000 as thenumber into #test --indirect declare select @a = ( select thenumber + 100 from #test ) update #test set thenumber = @a select * from #test 

It shows an error:

 Arithmetic overflow during implicit conversion of NUMERIC value '100.000' to a NUMERIC field . 

What in my head should work, but not like the thenumber column was not declared (or indirectly declared as decimal (4.3)). Thus, you will need to indirectly declare a temp table column with scale and precision in the desired format, since in my case it was 000,000.

 select 000.000 as thenumber into #test --this solved it 

Hope this helps someone :)