Approximate Data types

  • TomThomson (2/16/2015)


    Sean Lange (2/16/2015)


    Great back to basics question. I am shocked at the response rate.

    Correct answers: 57% (166)

    Incorrect answers: 43% (123)

    Total attempts: 289

    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    I don't think it's particularly surprising, it's quite natutral that a lot of eople don't remember this bizarre misuse of the word "approximate". In fact I would agree with Richard Warr if this nonsensical mis-naming wasn't clearly documented. It's somewhat encouraging that more than half of the wrong answers so far were "all of the above".

    I'm not sure I agree that it is "nonsensical mis-naming". What should they be called then to live up to your standards for naming?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (2/16/2015)


    TomThomson (2/16/2015)


    Sean Lange (2/16/2015)


    Great back to basics question. I am shocked at the response rate.

    Correct answers: 57% (166)

    Incorrect answers: 43% (123)

    Total attempts: 289

    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    I don't think it's particularly surprising, it's quite natutral that a lot of eople don't remember this bizarre misuse of the word "approximate". In fact I would agree with Richard Warr if this nonsensical mis-naming wasn't clearly documented. It's somewhat encouraging that more than half of the wrong answers so far were "all of the above".

    I'm not sure I agree that it is "nonsensical mis-naming". What should they be called then to live up to your standards for naming?

    I don;t understand the mis-naming part, but I certainly am not surprised by the number of incorrect responses.

    I doubt that many people know the actual differences between these types, regardless of the name!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Sean Lange (2/16/2015)


    TomThomson (2/16/2015)


    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    I don't think it's particularly surprising, it's quite natutral that a lot of eople don't remember this bizarre misuse of the word "approximate". In fact I would agree with Richard Warr if this nonsensical mis-naming wasn't clearly documented. It's somewhat encouraging that more than half of the wrong answers so far were "all of the above".

    I'm not sure I agree that it is "nonsensical mis-naming". What should they be called then to live up to your standards for naming?[/quote]

    Let's just call them all "numeric".

    Approximate is something we can say about values, not about types. A measurement or an estimate can deliver a value, and we hope the value delivered is a decent approximation of the value we were trying to measure or to estimate, and we call that measurement or estimate an approximation to the real value.

    A type on the other hand is a means of representing some values. It can represent some values exactly. Other values it can't represent at all (let's ignore types that have infinite storage available for value representation: we sometimes use them in maths, but not in the real world). Some of the values it can represent may be used as approximations for values it can't represent. For some types, it's normal practise to use representable values as approximations of values that aren't representable: that includes numeric, decimal, binaryexp (and hexexp etc) float, decimalexp float, and rather a lot of other types; maybe it even includes integer types - for example we could use 255 as a tinyint approximation of 256, with an error something under 0.4%, but generally it isn't interesting to think of whole number types as being used to approximate anything much. So if we were going to call some types "approximate" it would be less silly to call all the non-whole number types that than to call float (for example) that but not call decimal(12, 5) that. As a mathematician by training I abhor calling any types approximate because they represent exact values, not approximations - it is only one of the uses we can choose to make of some of the values they can represent that is approximation, and that use is probably more often made of values expressed in some of the so-called "exact" numeric types.

    As for the "decimal" types being "exact" numerics, just try representing 1/3 in decimal(20,2) you will have an error of (at least, and probably exactly unless you are careless) 1%. If I try real instead the error will be more than a million times smaller than that. And of course float will take you to better than a quadrillion times less error in that approximation.

    Tom

  • TomThomson (2/16/2015)


    Sean Lange (2/16/2015)


    TomThomson (2/16/2015)


    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    I don't think it's particularly surprising, it's quite natutral that a lot of eople don't remember this bizarre misuse of the word "approximate". In fact I would agree with Richard Warr if this nonsensical mis-naming wasn't clearly documented. It's somewhat encouraging that more than half of the wrong answers so far were "all of the above".

    I'm not sure I agree that it is "nonsensical mis-naming". What should they be called then to live up to your standards for naming?

    Let's just call them all "numeric".

    Approximate is something we can say about values, not about types. A measurement or an estimate can deliver a value, and we hope the value delivered is a decent approximation of the value we were trying to measure or to estimate, and we call that measurement or estimate an approximation to the real value.

    A type on the other hand is a means of representing some values. It can represent some values exactly. Other values it can't represent at all (let's ignore types that have infinite storage available for value representation: we sometimes use them in maths, but not in the real world). Some of the values it can represent may be used as approximations for values it can't represent. For some types, it's normal practise to use representable values as approximations of values that aren't representable: that includes numeric, decimal, binaryexp (and hexexp etc) float, decimalexp float, and rather a lot of other types; maybe it even includes integer types - for example we could use 255 as a tinyint approximation of 256, with an error something under 0.4%, but generally it isn't interesting to think of whole number types as being used to approximate anything much. So if we were going to call some types "approximate" it would be less silly to call all the non-whole number types that than to call float (for example) that but not call decimal(12, 5) that. As a mathematician by training I abhor calling any types approximate because they represent exact values, not approximations - it is only one of the uses we can choose to make of some of the values they can represent that is approximation, and that use is probably more often made of values expressed in some of the so-called "exact" numeric types.

    As for the "decimal" types being "exact" numerics, just try representing 1/3 in decimal(20,2) you will have an error of (at least, and probably exactly unless you are careless) 1%. If I try real instead the error will be more than a million times smaller than that. And of course float will take you to better than a quadrillion times less error in that approximation.

    Works for me. Let's drop float and real as datatypes. Might as well drop money and smallmoney (long overdue really). So really it isn't so much the naming of the datatypes that irritates you as it is the fact that they can't store data consistently?

    --edit--

    Fixed a missed quote.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (2/16/2015)


    TomThomson (2/16/2015)


    Sean Lange (2/16/2015)


    TomThomson (2/16/2015)


    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    I don't think it's particularly surprising, it's quite natutral that a lot of eople don't remember this bizarre misuse of the word "approximate". In fact I would agree with Richard Warr if this nonsensical mis-naming wasn't clearly documented. It's somewhat encouraging that more than half of the wrong answers so far were "all of the above".

    I'm not sure I agree that it is "nonsensical mis-naming". What should they be called then to live up to your standards for naming?

    Let's just call them all "numeric".

    Approximate is something we can say about values, not about types. A measurement or an estimate can deliver a value, and we hope the value delivered is a decent approximation of the value we were trying to measure or to estimate, and we call that measurement or estimate an approximation to the real value.

    A type on the other hand is a means of representing some values. It can represent some values exactly. Other values it can't represent at all (let's ignore types that have infinite storage available for value representation: we sometimes use them in maths, but not in the real world). Some of the values it can represent may be used as approximations for values it can't represent. For some types, it's normal practise to use representable values as approximations of values that aren't representable: that includes numeric, decimal, binaryexp (and hexexp etc) float, decimalexp float, and rather a lot of other types; maybe it even includes integer types - for example we could use 255 as a tinyint approximation of 256, with an error something under 0.4%, but generally it isn't interesting to think of whole number types as being used to approximate anything much. So if we were going to call some types "approximate" it would be less silly to call all the non-whole number types that than to call float (for example) that but not call decimal(12, 5) that. As a mathematician by training I abhor calling any types approximate because they represent exact values, not approximations - it is only one of the uses we can choose to make of some of the values they can represent that is approximation, and that use is probably more often made of values expressed in some of the so-called "exact" numeric types.

    As for the "decimal" types being "exact" numerics, just try representing 1/3 in decimal(20,2) you will have an error of (at least, and probably exactly unless you are careless) 1%. If I try real instead the error will be more than a million times smaller than that. And of course float will take you to better than a quadrillion times less error in that approximation.

    Works for me. Let's drop float and real as datatypes. Might as well drop money and smallmoney (long overdue really). So really it isn't so much the naming of the datatypes that irritates you as it is the fact that they can't store data consistently?

    --edit--

    Fixed a missed quote.

    No, it's not remotely that. It's that the names are utterly misleading. And that calling a type approximate is just plain crazy - what's it supporsd to be approximate to - some other type? ANd calling decimal types "exact" is even crazier, all of them are at least as limited in the values they can represent exactly as are the so-called "approximate" types, and most of them are far more limited.

    I wish we could have the latest floating point standard in SQL Server. It includes decimal-exp floats, and 256 bit formats, so it can provide exact representation of anything the decimal types currently can and a good deal more, and will do arithmetic faster than decimal does because the mantissas are still binary. It also caters for sensible error handling, which no numeric type currently in SQL supports, and for very small and very large numbers which are sometimes rather useful. Maybe with that we could scrap all the current non-int numeric types (except perhaps the current float extended to allow quad length, which also has its uses).

    Tom

  • Sean Lange (2/16/2015)


    Great back to basics question. I am shocked at the response rate.

    Correct answers: 57% (166)

    Incorrect answers: 43% (123)

    Total attempts: 289

    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    They didn't get it wrong. They got it approximately right.

  • I guess numeric is not an approximate, it's an exact.

  • sknox (2/16/2015)


    Sean Lange (2/16/2015)


    Great back to basics question. I am shocked at the response rate.

    Correct answers: 57% (166)

    Incorrect answers: 43% (123)

    Total attempts: 289

    I am really surprised that so many people got this wrong. Obviously this is a topic which needs some more attention if this many people are confused about what is an exact numeric and approximate. :w00t:

    They didn't get it wrong. They got it approximately right.

    😀

  • I came late to the game. Nice one!

    Not all gray hairs are Dinosaurs!

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply