Clever Geek Handbook

Why is there a performance difference?

select ROUND(5.8990,0) output is 6.000

select ROUND(643/109,0) output is 5

Why is this difference in output?

+3


source to share


7 replies


This is due to the use of integer division.

> select 643/109;
5
> select round(643/109, 0);
5.0
> select 643/109.0;
5.89908256880734
> select round(643/109.0, 0);
6.0


The first command shows that integer division is occurring. So, change the divisor or dividend to float and you get the expected result as shown in the last command.

+2


source


select 643/109

gives the coefficient 5

. So select ROUND(5,0)

there is5



0


source


This is because int / int only returns the int part.

Try the following:

select Round(Cast(643 as decimal)/cast(109 as decimal),0)
0


source


A workaround would be to simply add a decimal point to any of the numbers. Then the parameter of the round function will be decimal, not integer.

0


source


If you want to get the decimal part you have to use float or decimal too, see below code once

select ROUND(CONVERT(Decimal(18,2),643)/CONVERT(Decimal(18,2),109),0) output is 6.000
0


source


In your first query, the select ROUND(5.8990,0)

output is 6.000 since the value, 5.8990, is considered decimal due to the presence of a dot. But in the case of the second query, select ROUND(643/109,0)

643 and 109 are considered to be integers, so the return value is 5 and not 5.8990 as you expected. In order to change the return to 6.000, all you have to do is pass a second query like this:

select ROUND(643.00/109.00,0)

enter image description here

0


source


In the second case, you are dividing two integers, so you end up with an integer. The split result is 5.89, but it will just discard all the leftovers because it needs an integer as its return, so you get 5. The result is truncated. Additional Information

If you want to have a decimal result, just use the decimal number in any part of the division, for example

select ROUND(643.0/109,0) -- returns 6
-- or
select ROUND(643/109.0,0) -- also returns 6

In the first case, ROUND does not truncate the result, it is rounded up to the next nearest integer (in accordance with the parameters that you specified). You can get the same result if you use

select ROUND(5.8990, 0, 1) 
-- returns 5

More information about ROUND here

0


source






More articles:


All Articles