May 22, 2012 at 7:42 am
Yes, I can see a reason for having an 'integer division' operation. That doesn't mean it needed to be the default behaviour whenever two integers are divided.
And it isn't as universal as you imply to have INT/INT yielding another INT, even in other MS products: in VBA / VBScript there is a distinct integer division operator ,which is always available if you need it, but you need to make a conscious choice to use it:
Sub test()
Dim a As Integer, b As Integer
a = 1
b = 2
Debug.Print a / b 'returns 0.5
Debug.Print a \ b 'returns 0
End Sub
May 22, 2012 at 8:13 am
archie flockhart (5/22/2012)
Yes, I can see a reason for having an 'integer division' operation. That doesn't mean it needed to be the default behaviour whenever two integers are divided.And it isn't as universal as you imply to have INT/INT yielding another INT, even in other MS products: in VBA / VBScript there is a distinct integer division operator ,which is always available if you need it, but you need to make a conscious choice to use it:
Sub test()
Dim a As Integer, b As Integer
a = 1
b = 2
Debug.Print a / b 'returns 0.5
Debug.Print a \ b 'returns 0
End Sub
And in Pascal you have div operator. I'm not sure about division operators in Shakespeare and in Turing machine there are no division operators at all.
In SQL Server the behaviour is defined and described. You may complain, you may disagree, you may protest, but SQL will divide the same way.
May 22, 2012 at 8:32 am
I am a little disapointed that SQL does not convert, in any standard division, the numbers in decimal just in case that the division might be a decimal answer (4/2 is integer, but 5/2 is decimal). People could specify a convertion if needed, but I am sure that this behavior would not be liked by every one.
My experience: always specify what type you want because of the possibility of a non standard division "round up" l(ike 5/2 = 3 intead of 2 because it roud the .5 in the higher number) in a futur updated version of the application even if you think that it is impossible that "they" would do that!
Nice question. Learning every day!
May 22, 2012 at 8:42 am
archie flockhart (5/22/2012)
Yes, I can see a reason for having an 'integer division' operation. That doesn't mean it needed to be the default behaviour whenever two integers are divided.
What would you like the type of the result to be, if not integer? Float? Real? Decimal(38,19)?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 8:44 am
Hugo Kornelis (5/22/2012)
Good question (though a bit old - I think we've had lots of questions about this subject already). But not a good explanation.There is no data type conversion. Both operands are the same data type (int - implicitly derived from the format used for the constants), so nothing has to be converted. The correct term that had to be used in the explanation is "integer division".
This is the point that I came here to make (and as usual Hugo arrived earlier). Nevertheless, I still have the link I was going to give on my clipboard, so here it is: Constants (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 8:48 am
SQL Kiwi (5/22/2012)
This is the point that I came here to make (and as usual Hugo arrived earlier).
Sorry, Paul... :Whistling: (You can always try blaming time zones - though I'm not sure if many people would fall for it)
Thanks for the additional link!
May 22, 2012 at 8:49 am
archie flockhart (5/22/2012)
Yes, I can see a reason for having an 'integer division' operation. That doesn't mean it needed to be the default behaviour whenever two integers are divided.And it isn't as universal as you imply to have INT/INT yielding another INT, even in other MS products: in VBA / VBScript there is a distinct integer division operator ,which is always available if you need it, but you need to make a conscious choice to use it:
Arturius's post was about ANSI-standards-compliant products, not Microsoft's decisions for non-ANSI-compliant products. However, let's ignore that for the moment and look at VBScript and VBA.
VBScript doesn't have data typing (well, technically it actually does, internally, but Microsoft hid it to try to make VBScript look and act more like Javascript) so it doesn't have INTs and FLOATs -- it just has numbers. Therefore, integer division in VBScript has to be specified in order to force the scripting engine to treat the general number objects as more specific INTs.
VBA (a subset of Visual Basic) DOES have data typing, but for general numbers it only has INTs and FLOATs (DECIMAL exists only as a subtype of VARIANT and primarily for interfaces with systems which pass data in DECIMAL format -- it's not a candidate for implicit conversion.) So if an implicit type conversion is desired, there's only one path (okay, two if you distinguish between single- and double-precision): 1/2 becomes 5.00E-01. In SQL Server, there are multiple options: should 1/2 be treated as 5.00E-01 (FLOAT) or as 0.5 (DECIMAL)? What about 1/3? Is it 3.33333333333333E-01, or 0.3, 0.33, 0.333, 0.3333, 0,33333, 0.333333, 0.3333333, 0.33333333, 0.333333333, etc?
Microsoft built VB and VBA for novice programmers. So they made a lot of assumptions and decisions about things like type conversion that they felt would get in the way of people trying to do (cough) basic tasks. They chose to make it compatible with the arithmetic most of us learned at school. The ANSI SQL standard, on the other hand, was written by professionals for professionals, and built into that was the expectation that SQL developers would want control over data type conversion and would be able to recognize where it was needed.
May 22, 2012 at 9:02 am
tks for the easy question. what a BONUS the conversations have been today! Cheers
May 22, 2012 at 9:03 am
Too easy, and I am surprised that there is any discussion...
May 22, 2012 at 9:09 am
I managed to get it wrong.
I thought the correct answer was too easy and maybe the 1 would default to a bit data type.
May 22, 2012 at 9:34 am
Nice and easy. Thanks!
May 22, 2012 at 9:51 am
[ In SQL Server, there are multiple options: should 1/2 be treated as 5.00E-01 (FLOAT) or as 0.5 (DECIMAL)? What about 1/3? Is it 3.33333333333333E-01, or 0.3, 0.33, 0.333, 0.3333, 0,33333, 0.333333, 0.3333333, 0.33333333, 0.333333333, etc?
Microsoft built VB and VBA for novice programmers. So they made a lot of assumptions and decisions about things like type conversion that they felt would get in the way of people trying to do (cough) basic tasks. They chose to make it compatible with the arithmetic most of us learned at school. The ANSI SQL standard, on the other hand, was written by professionals for professionals, and built into that was the expectation that SQL developers would want control over data type conversion and would be able to recognize where it was needed.
Its not something I'd get particularly stressed by. As others have said, the behaviour is defined and documented and won't be changing.
But to reply to Paul's direct question and to your list of possible results above: if I could choose, I'd return FLOAT data. It's the closest equivalent to the mathematical concept of dividing two integers to get a rational number.And if you wanted different behaviour, you'd CAST the values before calculating, as you do now, or CAST the result. I don't see anything more "novice" or less "professional" about those choices: it's just knowing and using the definitions provided in the tool that you are using.
Other "interesting" behaviour that results from the current definition:
A*B/C isn't the same as B/C*A . 2*3/4 <> 3/4*2
(A*B)/C isn't the same as A*(B/C). (2*3)/4 <> 2*(3/4)
(A+B)/C isn't the same as A/C + B/C. 2/3 + 2/3 <> 4/3
These are not small differences in an insignificant decimal digit, as you might get with FLOAT data and non-terminating decimals.
Also
A/B * B isn't the same as A. There is no 'inverse operation' to division. You can't solve the equation X/3=1 because the answer could be X= 3, X=4, or X=5.
(BTW, I'd view returning an integer zero as the result of SELECT 1/3 as just another equally arbitrary choice, albeit one made by a standards committee: it's the next result in sequence after 0.333, 0.33, 0.3 ... , and the one that throws away the maximum amount of information 🙂 )
May 22, 2012 at 10:04 am
archie flockhart (5/22/2012)
But to reply to Paul's direct question and to your list of possible results above: if I could choose, I'd return FLOAT data. It's the closest equivalent to the mathematical concept of dividing two integers to get a rational number.
The problem with that is that conversion from non-integer to integer always truncates. So if you assign the result of an int/int to an int variable, SQL Server would first calculate the result as a float, then truncate to integer. With a possible side effect that a division that should come out at an exact number now comes out at one less, because the float value is not (x).000001, but (x-1).99999.
Yes, I realize that you could also change the way non-integers are converted to integer to use rounding instead of truncation. But by then, you are talking about a completely different product.
I will admit that integer division has its problems, which can surprise you if you're not aware of them. But I think that any alternative for the result of an int/int division brings new problems; I don't think there's a solution that will always work.
May 22, 2012 at 10:18 am
Got stuck in meetings, so I wasn't able to get to QOTD until later than usual. But, I enjoyed the lively discussion.
Dredged up a lot of things I learned way back in the stone ages, and don't think about on a daily basis. (Sets being closed under certain operations, etc.) I loved computer science when it was SCIENCE. Now my job is more about protecting morons from themselves, and writing reports that won't be looked at when the crisis of the moment is over.
Hamiltonian Cycles? NP complete? Regular languages? Miss them - sniff.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 22, 2012 at 10:23 am
archie flockhart (5/22/2012)
HugoYou're right that as long as the behaviour is documented we can work with it - too late to change the behaviour now anyway ! - but a choice was made at some stage to build in the 'rule' that all arithmetic operations on two integers will return another integer.
Mathematically, that's true for addition, subtraction and multiplication - if we ignore the possibility of overflows which are a different issue. But it's not true for division: the set of integers is not closed under division. m/n where m and n are integers and n<>0 is pretty much the definition of Rational Numbers.
I think that you are wrong there. There are two integer division operations, neither of which produces a rational: the most commonly seen one is a perfectly good recursive division operation on integers, which produces an integer result, not a rational result; the other operates on the domain consisting of integers augmented by bottom, and is of course a partial recursive function, not a total recursive function: on pairs of integers where the total recursive doesn't provide a result which when multiplied by the dividor delivers the divident, the partial function delivers bottom (alternatively, the operation does not halt - the function doesn't deliver anything), and on all other pairs it delivers the same result as does the total recursive function. There is also a "divide with remainder" operation which is the first division operation most people learn at school, but this isn't an integer operation because it's result is not an integer but a pair of integers (quotient and remainder); this can be constructed using the total recursive integer division function along with the integer modulo function, since one delivers the quotient and teh other the remainder.
There is of course a perfectly good embedding operation on ordered pairs of integers into ordered pairs of rationals, and clearly that operation can be compounded with the rational division operation to provide an operation on two integers that produces a rational; that operation is not integer division.
If it makes sense at all to divide one integer by another ( e.g. number of staff divided by number of offices) I think it makes sense to return fractional answers. It is probably much less common to divide two integers and want the answer to be the nearest integer rounded towards zero. But I'd be interested if people have different experiences of this and are actually using integer division without CASTing, and for what ?
People certainly are using integer division. Any time you have a combinatorial problem on a countable discrete space you are more likely to find integer division useful than rational division. On the other hand, if you are working in a continuous space (probably not a combinatorial problem then) you are likely to want rational division, which you can get in SQL by using cast or convert on both operands (and if you are lazy, the conversion can be explicit on one and implicit on the other).
Tom
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply