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.