Question rounding TSQL numbers

Question

I have a code:

IF OBJECT_ID(N'dbo.rounding_testing') IS NOT NULL DROP FUNCTION dbo.rounding_testing; GO CREATE FUNCTION dbo.rounding_testing ( @value FLOAT, @digit INT ) RETURNS FLOAT BEGIN DECLARE @factor FLOAT, @result FLOAT; SELECT @factor = POWER(10, @digit); SELECT @result = FLOOR(@value * @factor + 0.4); RETURN @result; END; GO SELECT dbo.rounding_testing(5.7456, 3); SELECT FLOOR(5.7456 * 1000 + 0.4); 

Results:

 5745 5746 

I expect two 5746. I tried to debug the function and found interesting behavior. Below are some of the tests I conducted in the Immediate Window when debugging.

 @factor 1.000000000000000e+003 @result 5.745000000000000e+003 @value 5.745600000000000e+000 @value*@factor 5745.6 @value*@factor+0.4 5746 floor(@value*@factor+0.4) 5745 floor(5746) 5746 

Can someone help explain the result? Especially these three lines:

 @value*@factor+0.4 5746 floor(@value*@factor+0.4) 5745 floor(5746) 5746 

Answer 1

In the expression FLOOR(5.7456 * 1000 + 0.4); first, the part between the brackets is calculated. For constants, data types are derived based on notation; for 5.7456, which is decimal(5,4) ; 1000 - int ; and 0.4 is decimal(1,1) . The derived data type for 5.7456 * 1000 is decimal(10,4) ; and for the full expression it is decimal(11,4) . These are all exact numeric data types, so you won’t experience rounding; the final result is 5746,0000. The FLOOR function truncates the fraction and converts to decimal(11,0) , returning 5746.

A user function stores input parameters and intermediate results in a float data float (floating point data). This data type is intended to be used for approximate data, such as dimensions, where the data you read from the toolkit is already approximate. I studied in high school to read as many digits as possible, but treated the latter as insignificant - I had to keep it in all calculations, but ultimately to the number of significant digits based on the accuracy of my measurements, Rounding ensures that inaccuracies in the last digits will not affect the final result. Similarly, floating-point data types should be handled.

Internally, floating point numbers are represented in the base-2 number system. This means that there are numbers that have an exact representation in our often used base-10 system (for example, 5.7456), but the fractional part in base-2 never ends. (Just as, for example, one third, which can be represented exactly in base-3, has an infinite fractional part in base-10: 0.33333333333 (etc.)). The number of digits of the 2nd category used to store the float number is finite, so it must be trimmed at the end, which leads to the fact that it is rounded up or down using a tiny fraction. You can see this if you run:

 DECLARE @a float = 5.7456; SELECT CAST(@a AS decimal(19,16)); 

In this case, the clipping effect after a large number of digits of the 2nd level is that the stored value is 0.0000000000000004 less than the decimal value that you entered. This small difference turns into a huge effect due to the FLOOR function, which does exactly what it should do: round to the nearest integer.

(I have seen many people call this a mistake. This is not so. This is intentional and documented behavior. And the loss of accuracy here is no worse and no better than the loss of accuracy that you get when you store the third a DECIMAL(7,6) , this a little less obvious, because we all adults are used to working in the base 10)

Answer 2

You can fix it by changing float to real

 IF OBJECT_ID(N'dbo.rounding_testing') IS NOT NULL DROP FUNCTION dbo.rounding_testing; GO CREATE FUNCTION dbo.rounding_testing ( @value REAL, @digit INT ) RETURNS REAL BEGIN DECLARE @factor REAL, @result REAL; SELECT @factor = POWER(10, @digit); SELECT @result = FLOOR(@value * @factor + 0.4); RETURN @result; END; GO SELECT dbo.rounding_testing(5.7456, 3); SELECT FLOOR(5.7456 * 1000 + 0.4);