why does sql server return 0 for 1 / 2?

Question

In sql server when I do select 1 / 2 it returns 0 in stead of 0.5
Why is that?

Should not all divisions return a decimal value? Is there a setting I can set to make it divide normal?

I noticed the same in c#
What is the logic behind this?

At least one argument have to be float than return float value if both are integer than return integer. — Kaushik Maheta, Nov 04 '15 at 08:48
"Is there a setting I can set to make it divide normal" - define *normal*. Should the result be a `float`? or a `decimal`? In different contexts, either can make sense (as can `int`, which is what they currently do). — Damien_The_Unbeliever, Nov 04 '15 at 08:49
I dont understand, if I want truncation i would ask for it, but just dividing should return decimal like in real life — GuidoG, Nov 04 '15 at 08:49
Real life doesn't have limited precision data types. Most programming environments do, or require special handling for *arbitrary* precision data types. And not to mention, in real life, we can easily deal with non-terminating decimals. — Damien_The_Unbeliever, Nov 04 '15 at 08:51
Why is it more correct to change the type than to use the same type but discard the remainder? If you want that the decimal places are calculated and retained use decimal. — Tim Schmelter, Nov 04 '15 at 08:52
@GuidoG: you are clearly using two integers. Now the database has to make a decision on what to do with your division. It could use the more expensive way and calculate the exact value of this division and store it in a different type(decimal). But why? You have clearly stated that you want an integer because you have provided this type two times. It was easy for you to specify a decimal. But you haven't. So that's why the database does what you want (and what is cheaper), it's giving you an integer where the decimal places are truncated. — Tim Schmelter, Nov 04 '15 at 08:56
bye the way, thanks for all the downvotes, you guys really make it easy for anyone to ask questons here, such a welcome environment here — GuidoG, Nov 04 '15 at 08:56
@TimSchmelter I have nowhere stated that i want an integer, i have made a division and the result of a division cannot be stored into an int, so why does it do that. Should i throw all my math books away ? — GuidoG, Nov 04 '15 at 08:58
@GuidoG - "the result of a division cannot be stored into an int" - 4/2 can *very clearly* be stored in an `int`. Should the data type be decided on a case by case basis? — Damien_The_Unbeliever, Nov 04 '15 at 08:59
@GuidoG: that behaviour is consistent with many progamming languages like C or C#, VB.NET has two operators for this task. The "default" `/` has your desired behaviour, it returns a `Double` whereas \ uses integer division. There are questions on SO which explain why that decision was made like [this](http://stackoverflow.com/questions/9841332/why-is-the-division-result-between-two-integers-truncated). — Tim Schmelter, Nov 04 '15 at 09:00
no, not on a case by case basis, always decimal as a division can be decimal so choose a type that holds all results. the result of 4/2 can also be stored in an decimal, 1/2 cannot — GuidoG, Nov 04 '15 at 09:00
Why did you stop expanding it? Why are *four* decimal places obviously correct, when we're saying that *zero* decimal places are okay by us? — Damien_The_Unbeliever, Nov 04 '15 at 09:03
I still do not understand why a type has been choosen to hold results of a division that cannot hold every possible outcome of a division. — GuidoG, Nov 04 '15 at 09:44

score 7 · Accepted Answer · edited Apr 13 '17 at 12:19

7

Integer division

select 1 / 2  
-- 0

Float division (at least one argument have to be float/decimal):

select 1 / 2.0
-- 0.5

select 1.0 / 2
-- 0.5

select 1.0 / 2.0
-- 0.5

Divide

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

EDIT:

The point is you ask why? Becasue creator of language decided so, history, convention whatsoever.

I suggest read Is integer division uniquely defined in mathematics?.

Keep in mind that in some languages you have 2 division operators (one for integer division and one for real division).

Division Integer

Dividing integers in a computer program requires special care. Some programming languages, such as C, treat integer division as in case 5 above, so the answer is an integer. Other languages, such as MATLAB and every computer algebra system return a rational number as the answer, as in case 3 above. These languages also provide functions to get the results of the other cases, either directly or from the result of case 3.

Names and symbols used for integer division include div, /, \, and %. Definitions vary regarding integer division when the dividend or the divisor is negative: rounding may be toward zero (so called T-division) or toward −∞ (F-division); rarer styles can occur – see Modulo operation for the details.

^{For downvoters leave a comment so I can reply/improve my answer.}

edited Apr 13 '17 at 12:19

Community

1
1

answered Nov 04 '15 at 08:45

Lukasz Szozda

162,964
23
234
275

who came up with that idea ? – GuidoG Nov 04 '15 at 08:52
@GuidoG Creators of language/sql server engine. Your question is why and not how? I suggest to ask this question in [programmers.stackexchange.com](http://programmers.stackexchange.com/) `Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development.` Your question is rahter about history/convention. – Lukasz Szozda Nov 04 '15 at 08:55
@GuidoG - well, I can find division defined in these sort of terms at least as far back as the language BCPL, 1967. The C language inherited this sort of behaviour from there, and a *lot* of languages have taken C's basics as their own, for lack of better alternatives. – Damien_The_Unbeliever Nov 04 '15 at 09:00
It is easy to forget casting one value to decimal when your programming, the compiler wont complain so nobody notices it unil someone does a good unit test but even here it can go unnoticed. This makes small bugs appear in production environment, thats what happening here and I have to debug other peoples work again just to find another division again – GuidoG Nov 04 '15 at 09:10
@GuidoG For compiler perspective everything is correct. Think like this left side (int) / right side (int). Algorithm of division is precise. Why should compiler care about that the result is not float. It is responsibility of developer to check it. But I agree that 2 division operator would be clearer like `div and /` – Lukasz Szozda Nov 04 '15 at 09:11
Most of us here are coming from pascal, where every division returns a float. After 20 years of doing so you just dont switch thinking that easy – GuidoG Nov 04 '15 at 09:13
@GuidoG You made assumption that something should work this way. The point is that you should always check the environment you work with. – Lukasz Szozda Nov 04 '15 at 09:15
my problem comes from a team that has worked 20 years in a language where all divisions return a real, now we are in a language where it is not so and I can understand how one can easy forget to check for it sometimes. – GuidoG Nov 04 '15 at 09:17
@GuidoG Ok one more example that things can go badly. In programming world `null` = `null` or `nil` = `nil` do you agree? Does it work that way in Pascal? – Lukasz Szozda Nov 04 '15 at 09:18
ha, null = null cannot work in my mind, u cannot compare nothing with anything else – GuidoG Nov 04 '15 at 09:20
for testing if an object is assigned, you have to do if assigned(object) in pascal, writing if object <> nil compiles and sometimes works, but no guaranties when the variable is not the variable that was used to create the object but is a copy – GuidoG Nov 04 '15 at 09:22
if you create object, the compare will work, if you free the object, the compare will still return true when u forget to set the object back to nil after freeing it – GuidoG Nov 04 '15 at 09:23
@GuidoG The point is people make assumption. Like https://dotnetfiddle.net/tZhcgm. And then they ask the same class of question `how ....` can it work differently in SQL. Because they make assumption it works that way in C# I want the same in SQL. So the lesson for today, never take anything for granted :) – Lukasz Szozda Nov 04 '15 at 09:27

why does sql server return 0 for 1 / 2?

1 Answers1

Linked

Related