Arithmetic overflow error converting varchar to data type numeric. '10' <= 9.00

Question

Below is a subset of the kind of table structure and data i'm working with.

CREATE TABLE #Test
(
     Val varchar(5)
    ,Type varchar(5)
)

INSERT #Test VALUES ('Yes','Text')
INSERT #Test VALUES ('10','Int')
INSERT #Test VALUES ('10.00','Float')
INSERT #Test VALUES ('9.00','Float')
INSERT #Test VALUES ('9','Int')

I want to write a query that will let me know if the column 'Val' is <= 9.00 (must be of numeric data type). I did this by doing the following:

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

This gives me an arithmetic overflow error. However, if I exclude the row of data with the value '10':

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
        AND Val <> '10'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

It works without any issue. My question is not how to fix this as I know I can simply convert the data to the format I require.

My question is why the value of '10' in the column 'Val' is returning an error. Surely the logic should just return 'False' and simply exclude the rows because '10' (which I assume is implicitly converted) is greater than 9.00.

Thanks.

Answer 1

This generates an Arithmetic Overflow because it is trying to implicitly cast the Val column to a NUMERIC(3,2), which naturally will overflow on a 2-digit value like 10.

It's using NUMERIC(3,2) as the target type and size because that is the smallest numeric that 9.00 appears to fit into.

The solution, of course, is to use explict CASTing instead of doing it implicitly

Answer 2

From BOL:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

That means your constant 9.00 will have a precision of 1 and a scale of 0 a precision of 3 and a scale of 2, so it cannot store the value 10, which needs a minimum precision of 2 + scale.

You'll need to wrap the IntsOnly.Val with either a CAST or CONVERT to specify the correct precision and scale.