0

Decimal stores the exact value, where as flat stores an approximation.

Quote from an MSDN article:

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value

What is the example of value where an approximate value will be stored? For example say I store 100.15; when I retrieve I get the same value back. What is an example of value to show the approximation?

variable
  • 8,262
  • 9
  • 95
  • 215
  • It starts coming into play in values with very large precision. Have a look here: https://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server – squillman Jun 11 '21 at 12:27
  • Also probably relevant: [How are floating point numbers stored in memory?](https://stackoverflow.com/questions/7644699/how-are-floating-point-numbers-stored-in-memory) – Thom A Jun 11 '21 at 12:29
  • Suggest you read this [floating point guide](https://floating-point-gui.de/). And it is easy to get confused since an application might have rules for displaying floating point values that are not obvious. – SMor Jun 11 '21 at 12:51
  • Try `DECLARE @f FLOAT = 100.15; SELECT FORMAT(@f, 'G17')`. – Jeroen Mostert Jun 11 '21 at 12:57

1 Answers1

1

What is the example of value where an approximate value will be stored? For example say I store 100.15; when I retrieve I get the same value back. What is an example of value to show the approximation?

100.15 is a decimal value that cannot be stored exactly with float.

What tool are you using to view the value? SSMS will round the result whereas SQLCMD will not.

sqlcmd -Q"SELECT CAST(100.15 as float);"

Result:

------------------------
      100.15000000000001
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I might point out that many numbers in other bases cannot be stored in `decimal` so it's not really true to say that `decimal` is exact and `float` is not, just that `decimal` is exact for rational base-10 numbers up to the given precision, and `float` is exact for rational base-2 numbers up to the given precision – Charlieface Jun 11 '21 at 15:24