CODEWITHSUNDEEP
sqlsql-server-2008precision
Tannishtha Mukhopadhyay
Tannishtha Mukhopadhyay

Reputation: 41

precision gains where data move from one table to another in sql server

There are three tables in our sql server 2008

  1. transact_orders
  2. transact_shipments
  3. transact_child_orders.

Three of them have a common column carrying_cost. Data type is same in all the three tables.It is float with NUMERIC_PRECISION 53 and NUMERIC_PRECISION_RADIX 2.

  1. In table 1 - transact_orders this column has value 5.1 for three rows. convert(decimal(20,15), carrying_cost) returns 5.100000..... here.

  2. Table 2 - transact_shipments three rows are fetching carrying_cost from those three rows in transact_orders. convert(decimal(20,15), carrying_cost) returns 5.100000..... here also.

  3. Table 3 - transact_child_orders is summing up those three carrying costs from transact_shipments. And the value shown there is 15.3 when I run a normal select.

But convert(decimal(20,15), carrying_cost) returns 15.299999999999999 in this stable. And its showing that precision gained value in ui also. Though ui is only fetching the value, not doing any conversion. In the java code the variable which is fetching the value from the db is defined as double.

The code in step 3, to sum up the three carrying_costs is simple ::

...sum(isnull(transact_shipments.carrying_costs,0)) sum_carrying_costs,...

Any idea why this change occurs in the third step ? Any help will be appreciated. Please let me know if any more information is needed.

Upvotes: 1

Views: 236

Answers (1)

MatBailie
MatBailie

Reputation: 86765

Rather than post a bunch of comments, I'll write an answer.


Floats are not suitable for precise values where you can't accept rounding errors - For example, finance.

Floats can scale from very small numbers, to very high numbers. But they don't do that without losing a degree of accuracy. You can look the details up on line, there is a host of good work out there for you to read.

But, simplistically, it's because they're true binary numbers - some decimal numbers just can't be represented as a binary value with 100% accuracy. (Just like 1/3 can't be represented with 100% accuracy in decimal.)


I'm not sure what is causing your performance issue with the DECIMAL data type, often it's because there is some implicit conversion going on. (You've got a float somewhere, or decimals with different definitions, etc.)

But regardless of the cause; nothing is faster than integer arithmetic. So, store your values are integers? £1.10 could be stored as 110p. Or, if you know you'll get some fractions of a pence for some reason, 11000dp (deci-pennies).

You do then need to consider the biggest value you will ever reach, and whether INT or BIGINT is more appropriate.

Also, when working with integers, be careful of divisions. If you divide £10 between 3 people, where does the last 1p need to go? £3.33 for two people and £3.34 for one person? £0.01 eaten by the bank? But, invariably, it should not get lost to the digital elves.

And, obviously, when presenting the number to a user, you then need to manipulate it back to £ rather than dp; but you need to do that often anyway, to get £10k or £10M, etc.


Whatever you do, and if you don't want rounding errors due to floating point values, don't use FLOAT.

(There is ALOT written on line about how to use floats, and more importantly, how not to. It's a big topic; just don't fall into the trap of "it's so accurate, it's amazing, it can do anything" - I can't count the number of time people have screwed up data using that unfortunately common but naive assumption.)

Upvotes: 1

Related Questions