Have a table of string numbers where all values are '0.80 to '1.10'. Conversion to a tinyint lookup index gives an arithmetic error for one value only... '1.06' and I cant see why.
select @@version
select 1.06 * 200
select convert(tinyint, 1.06 * 200)
select convert(real, '1.06') * 200
select convert(tinyint, convert(real, '1.06') * 200)
gives...
Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)
212.00
212
212
211
This seems to be a classical case of rounding errors.
Check this code:
declare @val float = convert(real, '1.06') * 200;
select @val; --211,999984741211
You might consider converting to float instead of real, but even then it might be possible that you get rounding errors.
You might consider converting to decimal or numeric, since those types behave somewhat differently regarding rounding. But those types use more memory and are slower than real and float.
And perhaps you might also want to check if rounding functions (like ROUND and FLOOR) might be useful for you.
From a design perspective, it would almost always be a better choice to store numerical data in numerical types instead of strings. (Numerical data is intended to be used for the purpose of performing calculations. Besides that, converting a numeric value to a string is technically easier and more flexible than converting a string to a numerical value.)
It's up to you to investigate these (and other?) possible solutions and then choose which solution would be a good fit for your issue at hand.
select convert(tinyint, convert(decimal(3,2), '1.06') * 200)
select convert(tinyint, convert(real, '1.06000001') * 200)
both give correct 212.
I'll use decimal but confused why such a rounding error when only 2 decimal places used.