0

I'm storing a value (0.15) as a Real datatype in a Quantity field in SQL.

Just playing around, when I cast as numeric, there are some very slight changes to scale.

I'm unsure why this occurs, and why these particular numbers?

select CAST(Quantity AS numeric(18,18)) -- Quantity being 0.15

returns

 0.150000005960464480
proggrock
  • 3,239
  • 6
  • 36
  • 51

1 Answers1

1

Real and float are approximate numerics, not exact ones. If you need exact ones, use DECIMAL. The benefit of the estimated ones is that they allow storing very large numbers using fewer storage bytes. https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

PS:Numeric and decimal are synonymous.

PS2: See Eric's Postpischil clarification comment below: "Float and real represent a number as a significand multiplied by a power of two. decimal represents a number as a significand multiplied by a power of ten. Both means of representation are incapable of representing all real numbers, and both means of representation are subject to rounding errors. As I wrote, dividing 1 by 3 in a decimal format will have a rounding error"

SQLRaptor
  • 671
  • 4
  • 14
  • Ok, so it's showing the value that was ACTUALLY stored by SQL, this is the estimate and not the value I gave it. For sure this data type should be decimal. – proggrock Apr 17 '19 at 15:37
  • `Real` and `float` are not estimated while `decimal` is exact. `Real` and `float` are binary-based while `decimal` is decimal-based. Divide 1 by 3 in `decimal`, and you will get a rounding error. – Eric Postpischil Apr 17 '19 at 15:38
  • the correct term is approximate and not estimated. I will correct the answer. – SQLRaptor Apr 17 '19 at 15:40
  • @EricPostpischil all data is stored in binary form. Float and real use an approximation algorithm which gives up accuracy in favor of storage efficiency. – SQLRaptor Apr 17 '19 at 15:42
  • 1
    @SQLRaptor: `Float` and `real` represent a number as a significand multiplied by a power of two. `decimal` represents a number as a significand multiplied by a power of ten. **Both** means of representation are incapable of representing all real numbers, and **both** means of representation are subject to rounding errors. As I wrote, dividing 1 by 3 in a decimal format will have a rounding error. – Eric Postpischil Apr 17 '19 at 15:46
  • @EricPostpischil I see what you mean now. Thanks for the explanation. – SQLRaptor Apr 17 '19 at 15:51
  • @EricPostpischil you may find this interesting - https://www.nextplatform.com/2019/07/08/new-approach-could-sink-floating-point-computation/ – SQLRaptor Aug 07 '19 at 17:18