A few days back Steve Jones (b/t) asked me an interesting question. What is the result of the following query?

SELECT ROUND(0.7,0)

Now before you guess (and don't run it yourself) it might help to know a few things about ROUND. It rounds the first value passed to the number of positions specified by second value. An example is going to explain this best though:

SELECT ROUND(.123,2) -- Returns .12
SELECT ROUND(123,-2) -- Returns 100

So have you figured out what the first command returns? Unless you ran it yourself I'll bet a fair percentage of you got it wrong. It does not in fact return 1. You get this instead:

Msg 8115, Level 16, State 2, Line 6 Arithmetic overflow error converting expression to data type numeric.

Well, those are rather odd results. Let's try something a little different. You've probably tried something like this before, right?

SELECT 2560/1024 -- The table is 2560 MB and we need to know GB.

This returns 2, but let's say we need to be a bit more precise. The actual answer is 2.5. However, if we want the decimal value we need to do this:

SELECT 2560/1024.0 -- Add a .0 decimal to the end of one of the numbers.

But why? First of all constants have a data type just like columns and variables. It's pretty obvious that a string is a string but the specifics for numbers are a bit less obvious. 1 is a int, 45 is an int and 12.345 is a numeric(5,3). Any number without a decimal (up to max int) is an int. Anything else is a numeric with exactly the precision and scale required to hold it. That means that our 0.7 from earlier is going to be numeric(1,1).

ROUND on the other hand returns the same datatype as the value passed in (there are a few minor exceptions you can find in the BOL entry of ROUND). So since in this case it's going to return a numeric(1,1). Unfortunately the correct result, 1.0, won't fit. Numeric(1,1) will only hold 0.0 to 0.9, so you get the overflow error.

As far as the 2560/1024.0 example, when two objects (constant, variable or column) with different data types are combined then the data type of the output is going to be one or the other. Which one is determined by data type precedence. The type with higher precedence wins out. If an implicit conversion isn't allowed then you get an error. For example, Date to int.

So in the MB to GB calculation, we had int vs numeric(5,1). Numeric has a higher precedence so the output is numeric. Calculating what precision and scale it comes up with is beyond me I'm afraid.

FYI If you want to figure out which data type is used for a constant you can do this:

SELECT 2560 AS Num1, 1024.0 AS Num2, 2560/1024.0 AS Num3 
     INTO TempTable 
EXEC sp_help TempTable