CEILING returns FLOOR result - SQL SERVER 2008 R2

Question

DECLARE @a int;
DECLARE @b int;

SET @a = 9;
SET @b = 2;

SELECT CEILING (@a/@b);

It is returning as 4 instead of 5. Why?

Edit: I would like to get next smallest integer if the quotient is not whole number.

Answer 1

Try:

SELECT CEILING (@a/CAST(@b AS float))

And consider NULLIF(@b,0) too, to avoid a Division By Zero error.

Answer 2

After dividing 9 by 2 a decimal fraction is Truncated to its integer part - 4, not Rounded to 5. Try:

SELECT 9/2

Resilt is 4. Then CEILING(4) = 4

To get next integer declare variables as data types that can handle decimal part: NUMERIC,FLOAT, REAL.

Answer 3

SQL Server does integer division. So 9/2 = 4 in SQL Server.

Taking the ceiling of an integer is the same integer.