decimal vs real datatype

Posted on 2006-05-18
Last Modified: 2012-05-05
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.

Question by:lunchbyte
    LVL 9

    Expert Comment

    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.

    LVL 12

    Expert Comment

    my experience:
    MS (excel and access) front-ends handle ( compares, display) decimal datatype better than real
    LVL 5

    Accepted Solution

    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.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now