June 26, 2008 at 5:02 pm
JohnG (6/26/2008)
For financial systems, accuracy is paramount.So I'd use DECIMAL
And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 9:19 pm
I came to programming from computational physics. In calculations I often used doubles instead of floats. double is probably the least used datatype in sql server.
create table test (MyMoney double precision, YourMoney tinyint)
I haven't yet come across a database that used it though.
---------------------------------------
elsasoft.org
June 27, 2008 at 12:06 am
cute point about vardecimal... thought about using it, but it doesn't ship with express or standard editions... shame!
June 27, 2008 at 7:08 am
Jeff Moden (6/26/2008)
JohnG (6/26/2008)
For financial systems, accuracy is paramount.So I'd use DECIMAL
And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)
Speaking as a certified "bean counter" (yes, I have an accounting degree), the system has to be designed with the proper precision and scale for the monetary values that will be stored/computed/worked on. Rounding, when appropriate, is done. E.g., the "tax" on a purchase is rounded to (U.S.) pennies. Your mutual fund shares are only managed to 3 decimal places. Other systems, such as I worked on before, may require the full precision.
Therefore, know the datatypes and how they operate. Then make the appropriate choice working with the business experts when designing the system. Don't make an arbitrary choice -- it will come back to haunt you.
June 27, 2008 at 7:50 am
JohnG (6/27/2008)
yes, I have an accounting degree
Heh... nothing real evil meant here, John... but when I see something like that, I remember all the folks that I've interviewed that had CS degrees and/or MS certifications out the wahzoo that still couldn't write any kind of decent code. I've seen your posts in the past and, no doubt, you have some good experience in accounting and I'd trust that experience. But, just having a degree in something is no assurance of competency. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2008 at 8:06 am
But, just having a degree in something is no assurance of competency.
Very true. With all disciplines, it is one thing to be "book smart", it is another thing to be able to apply that knowledge.
As for me, CS degrees didn't exist at the time (oops! I'm giving something away). The Business training and degree helped me to to use (apply) the [computer] "tools" in the proper manner.
June 27, 2008 at 9:13 am
jezemine (6/26/2008)
I came to programming from computational physics. In calculations I often used doubles instead of floats. double is probably the least used datatype in sql server.create table test (MyMoney double precision, YourMoney tinyint)
I haven't yet come across a database that used it though.
In SQL Server, double = float(53)
* Noel
June 27, 2008 at 10:10 am
noeld (6/27/2008)
In SQL Server, double = float(53)
maybe so, but double precision looks better. like you are doing super-fine carpentry work or something, so you need to be doubly precise:
declare @chamfer double precision
set @chamfer = 2.883883838883880901111
select @chamfer/3.0
plus it's the only datatype I know of with a space in the name. how can you beat that?
---------------------------------------
elsasoft.org
November 27, 2016 at 1:28 pm
I just played around with Jeff's example a little bit. It seems that all prime numbers exhibit this bad DECIMAL behavior, and that the bigger the prime number, the worse the output gets:
-- try with a bigger prime number...
DECLARE @dec DECIMAL (18,6) = 17, @flt FLOAT = 17;
SET @dec = 17;
SET @flt = 17;
SELECT 1/@dec * 17, 1/@flt * 17;
-- result 0.9999999999999999986
-- Assuming http://primos.mat.br/indexen.html is correct, use a BIG prime number...
-- The rounding error becomes more pronounced...
SET @dec = 104729
SET @flt = 104729;
SELECT 1/@dec * 104729, 1/@flt * 104729;
-- result 0.9999999999999941421
-- ...but DECIMAL seems to handle non-prime numbers okay...
SET @dec = 100000
SET @flt = 100000;
SELECT 1/@dec * 100000, 1/@flt * 100000;
-- result 1.0000000000000000000
November 27, 2016 at 1:44 pm
Jeff Moden (6/26/2008)
JohnG (6/26/2008)
For financial systems, accuracy is paramount.So I'd use DECIMAL
And, that is precisely why I'd use float instead... but, whatever... it's really hard to convince people even with the ol' 1/3*3 example I gave above where Decimal gives the wrong answer.;) So long as you are using enough scale, you'll probably do ok...:)
Having revisited several similar posts, I find that some of the automatic rounding SQL Server does in decimal and the 15 digit limitation of precision in float (and fact that it's a binary float made to look decimal) can pretty much screw you to the floor without warning with whatever you use. I'll never understand why they seem to have done it all the hard way. WTH... calculator chips on PCs are just a recent innovation, right? 😉 486DXs are a brand new technology, right? :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2016 at 4:43 pm
Jeff Moden (6/24/2008)
Heh... Decimal is prone to more "rounding" errors than Float ever will be... it's a matter of rounding to the correct scale... For example, what should the answer to 1/3*3 be? Depending on the scale, it'll be some collection of 9's after the decimal point... With Float, it will more correctly be "1".I know... extraordinary claims require extraordinary proof...
DECLARE @a DECIMAL(3,0), @b-2 DECIMAL(18,0),@F FLOATSET @a = 3
SET @b-2 = 3
SET @F = 3
SELECT 1/@A*3.0, 1/@B*3.0,1/@F*3.0
SELECT 1/@A*3 , 1/@B*3 ,1/@F*3
...
Again, my recommendation is that you make money calculations out to at least 4 decimal places and do the correct rounding if needed.
Hi Jeff,
I have to take issue with your extraordinary proof, as it is proof of nothing more than: converting a value to another precision and scale causes problems.
If you take a look at this slightly modified code and its results, you will see that your proof is allowing a conversion to a non-integer decimal in the final result (precision 9, scale 6 for @a), which is not the required result (precision 3, scale 0).
Once the result is converted back to the correct precision and scale, the result is correct.
DECLARE @a DECIMAL(3,0), @b-2 DECIMAL(18,0),@F FLOAT
SET @a = 3
SET @b-2 = 3
SET @F = 3
-- Check the types of the original values @a, @b-2, and @C
SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,
B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,
C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE
FROM (SELECT @a, @b-2,@F) AS X(A,B,C)
-- Check the types and values coming out of the extraordinary proof
SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,
B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,
C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE
FROM (SELECT 1/@A*3, 1/@B*3,1/@F*3) AS X(A,B,C)
-- Check the types and values coming out of the corrected proof
SELECT A AS A_VALUE, SQL_VARIANT_PROPERTY(A,'Precision') AS A_PRECISION, SQL_VARIANT_PROPERTY(A,'Scale') AS A_SCALE,
B AS B_VALUE, SQL_VARIANT_PROPERTY(B,'Precision') AS B_PRECISION, SQL_VARIANT_PROPERTY(B,'Scale') AS B_SCALE,
C AS C_VALUE, SQL_VARIANT_PROPERTY(C,'Precision') AS C_PRECISION, SQL_VARIANT_PROPERTY(C,'Scale') AS C_SCALE
FROM (SELECT CAST(1/@A*3 AS decimal(3,0)), CAST(1/@B*3 AS decimal(18,0)), 1/@F*3) AS X(A,B,C)
I wholeheartedly agree with your conclusion, though - use the correct data type, precision and scale for the job and perform calculations in float.
I cannot bear financials stored in float in the database as a simple check on SUM(amount) = total when using float will fail to match (a lot) because of the imprecision, so you end up either converting to a precise datatype or using a fudge such as ABS(SUM(amount) - total) < 0.01 just to *get around* the float problem.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 27, 2016 at 10:59 pm
Same here, Magoo. Absolutely agree. The problem is that most people won't be aware and will write the code in the "extraordinary proof" manner.
I'll also state that they could have made our lives a bit easier here. Especially when it comes to division and multiplication.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2016 at 9:41 am
I have this scenario. Start with an integer value e.g. 365000. Over a period of time (usually a year) I will add one record a day with a proportional amount (so in this very basic example I would add one record each day of 1000 for the next 365 days). The total of these records should add up to the initial integer value. So each day I calculate how much of the initial total remains, and how many days are left to allocate it, and add a new record with the correct proportional amount.
Now this example is a gross oversimplification as a number of variables can change (including the initial value and the period), but the golden rule still applies - the sum of the records at the end of the period should equal the initial total. It goes without saying, I rarely have a nice easy integer value like 365,000 to start with!
As you might imagine, there will be all sorts of precision issues here. I have chosen to store my calculated data as float, as I believe this gives me the best chance of all my records adding up to the correct amount (or as near as possible!). Storing with any other type (decimal, money etc.) will necessarily compromise on precision and therefore adding up the records will almost certainly mean I will get a larger difference than if I store my results as floats.
I have read advice earlier in this thread suggesting I not store my data as floats. Given this scenario does that advice still stand? I don't think so but would like to hear any thoughts people have.
Thanks,
Ash
p.s. this is financial data 😛
December 7, 2016 at 10:06 am
Hi Ash,
It all depends !
If each individual value is required to be used in a rounded (e.g. to 2 dp for currency) form at any time, then using float is going to be a potential problem.
The way to ensure the total of all individual values always adds up to the original amount is to subtract the total so far from the original amount for the last period, no matter what method you use for interim values.
The problem with float is this:
e.g. 1000 over 3 days = 333.33, 333.33 and 333.34 when stored as decimal to 2dp.
e.g. 1000 over 3 days = 333.3333333333..., 333.333333333..., 333.33333333333 when stored as float.
I know which I would rather see in financial reporting.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);December 7, 2016 at 11:48 am
A quotient is essentially a computed value. If the application is working with imprecise quotients, then perhaps it's best (from an accuracy, storage, and performance) to contain dividend and divisor in two simple integer columns.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply