Problem with arithmetical calculations

Hello, everyone.

I have some experience working with T-SQL, but now I am faced with problem which I never seen before.

I have query:

SELECT2/(9+1)*1000

This query returns 0, but it must return 200.

I have tried this in SQL 2000 and SQL 2005, but result is the same.

What is wrong?

B.R.

Girts

I have found solution for this problem - it looks very simple and stypid :

SELECT 2/(9*1.0+1)*1000

|||

It's very simple, yes, but stupid? Not at all.

Have you thought about what you're calculating?
How much is 2 divided by 10?
How would one write 0.2 using only integers..? You can't, so it's rounded down to 0.

So your real problem is not how it's calculated, but what datatypes are used to do the calculation. When you add the decimalpoint as you found out, it's no longer done by all integers, but with decimals instead, so the rounding doesn't have to happen.
Thus you get the expected result. =:o)

You can find out more in BOL if you look for 'datatypes' and 'datatype precedence'

=;o)
/Kenneth

|||

Well, this is kind of wierd looking (the bold):

SELECT 2/(9*1.0+1)*1000

But like KeWin is saying it does the trick because it causes the 1.0 to be converted to a numeric value. The same effect can be achieved by:

SELECT 2.0/(9+1)*1000

You really have to be careful to consider the datatype at each step of the evaluation of the equation.

Integer + Integer = Integer
Integer / Integer = Integer

select 1/2 = 0 --because in integer math, you truncate the result, not round
select 1%2 = 1 --% is the mod function, gives you the remainder of the operation
--mod is essential when working with integers

Now consider:

select 1.0 / 1 = 1.000000

Numeric / Integer = Numeric

A trick to use to see the exact type is to use a sql_variant dataype:

declare @.value sql_variant
set @.value = 1.0 / 1

--give precision for integer even though not technically part of datatype
select cast(sql_variant_property(@.value,'baseType') as varchar(10))+
'(' + cast(sql_variant_property(@.value,'precision') as varchar(10))+ ',' +
cast (sql_variant_property(@.value,'scale') as varchar(10)) + ')'

select @.value

Using this query you can see what type is chosen for the output of an expression, either a scalar value, or a mathematic equation.

For the 1.0 / 1 before, this returns:

1.000000

SQL Server chooses a datatype that will always be able to store the result of the equation safely with no loss of precision if possible.

The point is that you have to be really careful with math because one little mess up on a type and your numbers are meaningless:

select (1/2) * 5000.00
select (1.0/2.0) * 5000

the first one is 0, because 1/2 returns integer 0 * numeric 5000.00 = 0.00

the second one 2500.000000, since 1.0/2.0 returns numeric(8,6) 0.500000 * 5000

Hopefully this is a bit clear. Datatype conversion can be tricky. In BOL (for 2005), look up Data Types [SQL Server]; Converting. There is an implicit conversion char to tell you what types will convert to what types as needed.