Round, Ceiling, Floor Function on MS SQL Server

ROUND(), CEILING() and FLOOR() Examples
Example	Value
In this example you can see with a positive integer all three rounding functions return the same value.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1)SELECT CEILING(@value)SELECT FLOOR(@value)	6 6 6
In the second example even with a negative integer all three rounding functions return the same value.
DECLARE @value int SET @value = -11 SELECT ROUND(@value, 1)  SELECT CEILING(@value) SELECT FLOOR(@value)	-11 -11 -11
To prove the point with an integer, rounding is not possible.  Let's check out some other data types.
DECLARE @value int SET @value = -11.5 SELECT ROUND(@value, 2)  SELECT CEILING(@value) SELECT FLOOR(@value)	-11 -11 -11
With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example.  The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value.  In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.
DECLARE @value decimal(10,2)SET @value = 11.05 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2)SELECT ROUND(@value, 3)SELECT CEILING(@value)SELECT FLOOR(@value)	11.10 11.05 11.05 12 11
As is the case with the example above, the 6 in the second digit to the right of the decimal place is significant based on the various length parameters.
DECLARE @value decimal(10,2)SET @value = -14.46 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2)SELECT ROUND(@value, 3)SELECT CEILING(@value)SELECT FLOOR(@value)	-14.50 -14.46 -14.46 -14 -15
This example helps to illustrate the break down of rounded values across.  This example also proves the point that the CEILING and FLOOR functions round to the nearest integer.
DECLARE @value decimal(10,10) SET @value = .5432167890 SELECT ROUND(@value, 1)  SELECT ROUND(@value, 2) SELECT ROUND(@value, 3) SELECT ROUND(@value, 4) SELECT ROUND(@value, 5) SELECT ROUND(@value, 6) SELECT ROUND(@value, 7) SELECT ROUND(@value, 8) SELECT ROUND(@value, 9) SELECT ROUND(@value, 10) SELECT CEILING(@value) SELECT FLOOR(@value)	0.5000000000 0.5400000000 0.5430000000 0.5432000000 0.5432200000 0.5432170000 0.5432168000 0.5432167900 0.5432167890 0.5432167890 1 0
In the final example, with a float data type you can see the same type of behavior as was the case with the decimal example above.  In addition, the CEILING and FLOOR functions round to the nearest integer.
DECLARE @value float(10) SET @value = .1234567890 SELECT ROUND(@value, 1)  SELECT ROUND(@value, 2) SELECT ROUND(@value, 3) SELECT ROUND(@value, 4) SELECT ROUND(@value, 5) SELECT ROUND(@value, 6) SELECT ROUND(@value, 7) SELECT ROUND(@value, 8) SELECT ROUND(@value, 9) SELECT ROUND(@value, 10) SELECT CEILING(@value) SELECT FLOOR(@value)	0.1 0.12 0.123 0.1235 0.12346 0.123457 0.1234568 0.12345679 0.123456791 0.123456791 1 0