ROUND( Numeric, Scale)

Rounds the number given to the specified number of decimal places.

If a non-numeric is passed to ROUND an exception is thrown. If a float type is passed into Round an estimated result may occur due to reasons outlined below.

Floats are estimated types

There are some built in rounding strangeness around floating point values in general (do you remember the comp sci class that talks about float being an estimation of the number?). But if you start rounding numbers that are estimates you can end up with strange results.

If you need to round a data value do not use float. Float is one of the types that is an estimate. This is from the MSDN Docs on float.

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Casting a FLOAT value

You can still do a rounding operation on a float column, but you need to cast it first in order to prevent prevision loss.

select round(2.562,2) + round(2.577,2);
-- Gives the result 5.1400000000000006 due to math rounding of floats

select CAST(round(2.562,2) as decimal) + cast(round(2.577,2) as decimal);
-- Gives correct result because of 128 bit math

select round(CAST(2.562 as DECIMAL),2) + round(CAST(2.577 AS DECIMAL),2);
-- Gives correct result because of 128 bit math

Implementing a custom round function

Sometimes you just want a different round operation than .Net uses in general. Some VB users are used to the classic VB 6 rounding, some developers only want the rounding for banking, etc. You can implement a custom rounding function to meet your need. No matter what type of rounding you decide internally, make sure you use the DECIMAL type for the input. That is the highest precision type in .Net. Decimals are 128 bit values, where floats are only 64 bit.

CREATE FUNCTION [MyRound]
(@Operand Decimal,@Places Int)
RETURNS DECIMAL
AS
BEGIN
    DECLARE @x decimal;
    DECLARE @i int;
    set @x = @Operand * power(10,@Places);
    set @i = @x;
    set @x = @i + iif((@x - @i) >= .5,1,0);
    set @x = @x / power(10,@Places); RETURN(@x);
END

This creates a function that implements a custom round based upon the POWER and the places argument.

select MYROUND( 2.569, 2 ); -- 2.57
select MYROUND( 2.5649, 2 ); -- 2.56

You can then use it in your own application like the above.

Example

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
--RETURNS: 123.999 124

SELECT ROUND( 123.99994, 0);
--RETURNS: 124

SELECT ROUND(748.58, -1); -- 750
SELECT ROUND(748.58, -2); -- 700
SELECT ROUND(748.58, -3); -- 1000 (Crashes in SQL Server 2008)