SQL Server Automatic Rounding ?

Question

Hi fellows,

I am very confused by the following results:

PRINT 3.1415926535897931 /180

Console result = 0.01745329251994329500

DECLARE @whatTheHell float(53)
SET @whatTheHell  = 3.1415926535897931/180
PRINT @whatTheHell

Console result = 0.0174533

I don't understand because referring to this:

http://msdn.microsoft.com/en-us/library/ms131092.aspx

Sql Server Float should be equivalent to c# double. But when I compute this in c#:

double hellYeah = 3.1415926535897931 /180;

I get 0.017453292519943295...

Answer 1

I guess the "FLOAT" type just has a limit on precision. Books Online says with FLOAT(53) you should get up to 15 digits of precision - not sure if there's an inherent limitation whether those digits are before or after the decimal separator.

Try using decimal instead:

DECLARE @whatTheHell2 decimal(18,16)
SET @whatTheHell2  = 3.1415926535897931/180
PRINT @whatTheHell2

Gives me the result:

0.0174532925199433

Marc

Answer 2

From the SQL Server 2005 Books Online Data Type Conversion topic:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

So the following is more representative of what SQL Server is doing implicitly:

DECLARE @whatTheHell NUMERIC(21, 20)
SET @whatTheHell = 3.1415926535897931 / 180
PRINT @whatTheHell

Answer 3

Divide is not rounding. PRINT is rounding.

DECLARE
  @var1 float,
  @var2 float,
  @var3 float

SET @var1 = 3.1415926535897931
SET @var2 = 180

SET @var3 = @var1 / @var2
SELECT @var1/@var2 as Computed, @var3 as FromVariable

PRINT @var1/@var2
PRINT @var3

Answer 4

I think you're getting confused by the fact that PRINT implicitly converts numeric to character with the default setting for the STR function -- a length of 10 (see MSDN). Try PRINT STR(@wth, 20, 16) and you might be happier.

Answer 5

When you say that SQL float maps to C# double, I think you are assuming that the SQL float byte size is the same as the C# double byte size.

I would say that the C# double is the only floating point data type big enough, in C#, to store the SQL float.

Example:

C# Double = 8 Bytes Sql Float = 4 bytes

The easy fix for your problem is to use decimal or numeric in your SQL

Answer 6

PRINT 3.1415926535897931 /180 is being evaluated as decimal.

Float only resolves to 15 significant figures. You have 17 so it can't be float. The 180 becomes decimal through implicit conversion because of datatype precedence and the output scale and precision is based on these rules

The output 0.01745329251994329500 has 17 sig figs too. It must be decimal.

Now, SET @whatTheHell = 3.1415926535897931/180. The float conversion takes place as part of the assignment operator. Before that it is also decimal on the right hand side. Float is approximate, and it's rounding.

In c# it's all doubles because you don't have fixed point (unless you tell the compiler?)

Related questions:

Choosing the appropriate precision for decimal(x,y)

In SQL how can I convert a money datatype to a decimal?

SQL Server, where clauses comparisons with different types & default casting behaviour