Integer division in sql server

Question

In Microsoft SQL Server 2005, why do the following commands produce integer results?

SELECT cast(151/6 AS DECIMAL(9,2))
SELECT 151/6

Answer 1

Because 151 and 6 are integers and you are doing integer division, even before the cast.

You need to make sure at least one of the arguments is a float type:

SELECT 151.0/6

Or

SELECT 151/6.0

Answer 2

Yes that is standard behavior

do

SELECT 151/6.0

or

SELECT 151/(CONVERT(DECIMAL(9,2),6))

or

SELECT 151/(6 * 1.0)

Answer 3

In the first you are getting the result of two integers and then casting the result as DECIMAL(9,2). In the second you're just dividing two integers and that's expected.

If you cast one of the integers as a decimal BEFORE you do the division, you'll get a decimal result.

SELECT 151/CAST(6 AS DECIMAL (9,2))

Answer 4

For the same reason they would in c#, java and other mainstream languages.

Integer 'rithmatic, the CAST is after the maths...