Why does FLOAT give me a more accurate result than DECIMAL?

Question

I am looking for a separation result that is extremely accurate.

This SQL returns the following results:

SELECT (CAST(297282.26  AS DECIMAL(38, 30)) / CAST(495470.44 AS DECIMAL(38, 30))) AS ResultDecimal

SELECT (CAST(297282.26 AS FLOAT) / CAST(495470.44 AS FLOAT)) AS ResultFloat

Here is the exact result from WolframAlpha: http://www.wolframalpha.com/input/?i=297282.26%2F495470.44

I had the impression that DECIMAL would be more accurate than FLOAT:

“Due to the approximate nature of floating point data types and real data, do not use these data types when exact numerical behavior is required, for example, in financial applications, when performing rounding operations or checking equality. Instead, use integer, decimal, monetary or small data".

https://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

FLOAT , DECIMAL?

Answer 1

:

SELECT (CAST(297282.26  AS DECIMAL(15, 9)) / CAST(495470.44 AS DECIMAL(24, 2))) AS ResultDecimal

+0,599999991926864496699338915153

, ( 100 ):

0,5999999919268644966993389151530412187657451370862810705720405842980259326873264124495499670979362562...

, , SQL Server :

max precision = (p1 - s1 + s2) + MAX (6, s1 + p2 + 1) - 38

max scale = MAX (6, s1 + p2 + 1)

p1 p2 - , s1 s2 - .

(15-9 + 2) + MAX (6, 9 + 24 + 1) = 8 + 34 = 42.

SQL Server 38.

= MAX (6, 9 + 24 + 1) = 34

Answer 2

. , , , . , . / 38. https://msdn.microsoft.com/en-us/library/ms187746.aspx

float, 53. https://msdn.microsoft.com/en-us/library/ms173773.aspx

Answer 3

, , , FLOAT , , true. , .

CAST , FLOAT DECIMAL.

, :

SELECT 297282.26  / 495470.44  AS ResultNoCast

SQL Server DECIMAL datatype , . , CAST DECIMAL.

CAST :

, , , . .

From     | To       | Behavior
numeric  | numeric  | Round

, , NUMERIC ( , DECIMAL) , NUMERIC, .

, NUMERIC/DECIMAL, SQL Server, :

SELECT 297282.26000000  / 495470.44000000  AS ResultSuperPrecise

( ), , : , 0 . , , 23 .

Answer 4

, , .

@philosophicles. , , CAST , , " , ".

SELECT CAST((297282.26 / 495470.44) AS DECIMAL(38, 30)) AS ResultDecimal

30 , , 30. , CAST , , 30 . :

, , CAST , ? , , @philosophicles, , .

SELECT CAST(((297282.26/10000) / (495470.44/10000)) AS DECIMAL(38, 30)) AS ResultDecimal

?

:

, , , 38 .

https://dba.stackexchange.com/questions/41743/automatic-decimal-rounding-issue

​​ , .

https://dba.stackexchange.com/questions/41743/automatic-decimal-rounding-issue