NUMERIC_ROUNDABORT please give me the courtesy of a reach around

You'd have thought that I learned my lesson 2.5 years ago that SQL Server's default rounding rules are not the same as C++/C#, etc. Today however, I didn't think I was rounding. What I was doing however, was being lazy and letting implicit conversions happen.

It's a simple enough task, I had dumped data from a legacy system into a CSV and then delimited everything with tick marks and wrapped them with UNION ALL SELECT statements to turn my CSV into a massive select statement. I'm smart like that. Until, of course, I do something boneheaded like make rates (percentages) strings and then perform a mathematical operation on them.

;
WITH POP_QUIZ AS
(SELECT '5.75' AS foo)
SELECT 
    A.foo / 100.0 AS result0
,   A.foo / 100.00 AS result1
,   A.foo / 100.000 AS result2
FROM
    POP_QUIZ A

By eyeballing it, you can see that I'm simply converting 5.75% to .0575. I'm a savvy developer who knows that dividing by 100 is a different operation under the covers than division by 100.0. And since I want the floating point division, I use a floating point operand to ensure the right type of operation takes place. So, what are my results?

result0     result1       result2
----------- ------------- ----------------
0.058000    0.05750000    0.0575000000

That's not so helpful really. So, WTF is going on? SQL Server is looking at this character data and determines it needs to convert it to a decimal data type but it's cheating by looking at the other operand. It's numeric(4,1) for result0 and it's going to coerce the other operand foo, the string, into a numeric(4,1) to match. The only way to do that is to round but hey, our default is SET NUMERIC_ROUNDABORT OFF so SQL Server happily rounds up. It does not present a problem with result1 or result2 as there is no rounding going on but result0 sure bit me in the nethers today.

BOL covers some of this "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." but it just doesn't have the flashing warning text I apparently need to pay close enough attention.

References

http://msdn.microsoft.com/en-us/library/ms191530.aspx Data Type Conversion (Database Engine)