Asked  7 Months ago    Answers:  5   Viewed   47 times

What are the differences between numeric, float and decimal datatypes and which should be used in which situations?

For any kind of financial transaction (e.g. for salary field), which one is preferred and why?

 Answers

76

use the float or real data types only if the precision provided by decimal (up to 38 digits) is insufficient

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

  • Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators (Technet)

so generally because the precision provided by decimal is [10E38 ~ 38 digits] if your number can fit in it, and smaller storage space (and maybe speed) of Float is not important and dealing with abnormal behaviors and issues of approximate numeric types are not acceptable, use Decimal generally.

more useful information

  • numeric = decimal (5 to 17 bytes) (Exact Numeric Data Type)
    • will map to Decimal in .NET
    • both have (18, 0) as default (precision,scale) parameters in SQL server
    • scale = maximum number of decimal digits that can be stored to the right of the decimal point.
    • kindly note that money(8 byte) and smallmoney(4 byte) are also exact and map to Decimal In .NET and have 4 decimal points(MSDN)
    • decimal and numeric (Transact-SQL) - MSDN
  • real (4 byte) (Approximate Numeric Data Type)
    • will map to Single in .NET
    • The ISO synonym for real is float(24)
    • float and real (Transact-SQL) - MSDN
  • float (8 byte) (Approximate Numeric Data Type)
    • will map to Double in .NET
  • All exact numeric types always produce the same result, regardless of which kind of processor architecture is being used or the magnitude of the numbers
  • The parameter supplied to the float data type defines the number of bits that are used to store the mantissa of the floating point number.
  • Approximate Numeric Data Type usually uses less storage and have better speed (up to 20x) and you should also consider when they got converted in .NET
    • What is the difference between Decimal, Float and Double in C#
    • Decimal vs Double Speed
    • SQL Server - .NET Data Type Mappings (From MSDN)

Exact Numeric Data Types Approximate Numeric Data Types

main source : MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 Database Development - Chapter 3 - Tables , Data Types , and Declarative Data Integrity Lesson 1 - Choosing Data Types (Guidelines) - Page 93

Tuesday, June 1, 2021
 
williamcarswell
answered 7 Months ago
50

If you compare using:

if ('0' == 0) // if '0' is equal to 0

it should return true as the values are compared with the string being converted to a number. If you do:

if ('0' === 0) // if '0' is identical to 0

it will return false as they have to be of the same type too.

Note the triple '='

Wednesday, March 31, 2021
 
rob_mccann
answered 9 Months ago
18

float and double are floating binary point types. In other words, they represent a number like this:

10001.10010110011

The binary number and the location of the binary point are both encoded within the value.

decimal is a floating decimal point type. In other words, they represent a number like this:

12345.65789

Again, the number and the location of the decimal point are both encoded within the value – that's what makes decimal still a floating point type instead of a fixed point type.

The important thing to note is that humans are used to representing non-integers in a decimal form, and expect exact results in decimal representations; not all decimal numbers are exactly representable in binary floating point – 0.1, for example – so if you use a binary floating point value you'll actually get an approximation to 0.1. You'll still get approximations when using a floating decimal point as well – the result of dividing 1 by 3 can't be exactly represented, for example.

As for what to use when:

  • For values which are "naturally exact decimals" it's good to use decimal. This is usually suitable for any concepts invented by humans: financial values are the most obvious example, but there are others too. Consider the score given to divers or ice skaters, for example.

  • For values which are more artefacts of nature which can't really be measured exactly anyway, float/double are more appropriate. For example, scientific data would usually be represented in this form. Here, the original values won't be "decimally accurate" to start with, so it's not important for the expected results to maintain the "decimal accuracy". Floating binary point types are much faster to work with than decimals.

Tuesday, June 1, 2021
 
o_flyer
answered 7 Months ago
80

The answer is given in the comment by Ilja above:

The "namespace" is a so called schema and has to be declarated in the mapped object. Given the example from the opening post, the mapped table has to be defined like this:

class IndexedElements(Base):
    __tablename__ = "IndexedElements"
    __table_args__ = {"schema": "Reporting"}

    UniqueID = Column(String,primary_key=True)
    ValueDate = Column(DateTime)
    Value = Column(Float)

Or define a base class containing these informations for different schemata. Check also "Augmenting the base" in sqlalchemy docs: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#augmenting-the-base

Friday, August 27, 2021
 
ahmelsayed
answered 4 Months ago
74

You have two options. Fix your SQL Statement, or your Convert.

SQL - Wrap your SUM in an ISNULL:

SELECT ISNULL(SUM(payment), 0)

Convert - Compare it to DBNull.Value first:

var result = sc.ExecuteScalar();
int intResult = result == DBNull.Value ? 0 : Convert.ToInt32(result);
Monday, December 6, 2021
 
Simon David Pratt
answered 3 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share