[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3295
  • Last Modified:

decimal vs real datatype

I have been using real as datatype but on another forum it was suggested that we should use decimal(X,X). I noticed the numbers are not consistence when I use real compare to the VB datatype single. I am thinking of changing it over to decimals. I wanted to get your feedback before I do that.



0
lunchbyte
Asked:
lunchbyte
1 Solution
 
nito8300Commented:
Hi there.
I would go with decimal for precision

float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

------------------------------------

decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.



0
 
illCommented:
my experience:
MS (excel and access) front-ends handle ( compares, display) decimal datatype better than real
0
 
rmacfadyenCommented:
Real values, aka floating point, are _approximate_ values. This is because of the nature of how floating point numbers work... there is no getting around it. Some values will actuall be stored as, for example 2.0 may actually be stored as 1.99999999 which will generally be displayed as 2.0. However any math operations will be using the fractional portion... and if you do enough math operations eventually .9999999 portion becomes significant and you have a "rounding error".

The decimal datatype does not suffer from this. I'm not sure how exactly this is stored internally... probably as BCD, binary coded decimal, where each decimal digit consumes 1/2 a byte (one hex digit). This requires more storage for larger numbers... but has the distinct advantage of always being perfectly accurate. If you store 3.14 in a decimal field it actually stores 3.14, not an approximation of 3.14.

For any numbers dealing with currency or concrete amounts (number of shares) you _must_ use decimal values.

Rob
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now