June 17, 2009 at 2:12 am
Hi,
with my question, you can understand i'm a newbie with sql server 2005.
i must to create a table who contains some euros in one columns, and i'd like to know witch type of data i must to choice !?
decimal or numeric ?
Thanks for your time
Christophe
June 17, 2009 at 2:15 am
Hi
Numeric is equal to decimal. But did you have a look to data types MONEY/SMALLMONEY? 😉
Flo
June 17, 2009 at 4:26 am
Hello Christophe,
Definitely listen to Florian’s suggestion of using one of the Money Data types, unless you will not be storing Cents, in which case an Integer could be a good idea.
May be take a look at the topic “Data Types (Transact-SQL)” in SQL Server Books Online. It will give you a good overview of what Data Types are available. It includes hyperlinks to more details on each one.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 17, 2009 at 7:53 am
Please be aware that results of the calculation using the MONEY datatype are often incorrect, especially when dividing. Suggest you use NUMERIC with the appropriate scale.
Here is an example of calculation errors with MONEY:
AllMoney CastFloats CastNUMERIC_12_2
13525.0885 13530.5038673171 13530.503867317054808
DECLARE@Amt1MONEY
,@Amt2MONEY
,@Amt3MONEY
SET @Amt1= 55294.72;
SET @Amt2= 7328.75;
SET @Amt3= 1793.33;
SELECT(@Amt3 / @Amt2) * @Amt1as AllMoney
,(CAST(@Amt3 AS FLOAT) / CAST( @Amt2 AS FLOAT))
* CAST( @Amt1 AS FLOAT)as CastFloats
,(CAST(@Amt3 AS NUMERIC(12,2) ) / CAST( @Amt2 AS NUMERIC(12,2)))
* CAST( @Amt1 AS NUMERIC(12,2))as CastNUMERIC_12_2
SQL = Scarcely Qualifies as a Language
June 18, 2009 at 1:07 am
Hello Christophe,
An interesting point from Carl Federl, though it should not put you off using the Money Data Types for storing Euro values. You just need to be careful what Data Types you use when you need to perform accurate calculations.
You could add the following line of code to Carl’s example, to see what I mean:-
, Cast((CAST(@Amt3 AS NUMERIC(12,2) ) / CAST( @Amt2 AS NUMERIC(12,2))) * CAST( @Amt1 AS NUMERIC(12,2)) As Money) as StoreAsMoney
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 18, 2009 at 1:18 am
Hi All,
I am really interested to know why using MONEY data type in calculations can return incorrect results? Is there a sql legend out there who can give us all an explanation. I have also heard that FLOAT can return erroneous results so if anyone can enlighten me I'd appreciate it.
Regards,
William
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 18, 2009 at 1:57 am
I have to admit that Carl's post did surprise me. I wasn't aware of problems with accuracy of money data type, and suddenly he came up with something that I defiantly didn't expect to happen. I'll also be very glad to hear an explanation about it from one of the form's Gurus.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 18, 2009 at 8:36 am
The problems with the money datatype has been know for decades (I knew about in 1993 from working with Sybase) and the problems have been posted by SQL Server MVPs many times, especially by Aaron Bertrand. Search the MS SQL Server Programming forums to see some posts.
For numeric precision and scale of the results of calculations, there is a good description by the "SQL Programmability & API Development Team Blog" titled "Multiplication and Division with Numerics" at http://blogs.msdn.com/user/Profile.aspx?UserID=25292
What is not mentioned in the blog are the results of aggregates on NUMERIC datatypes, which are
SUM will have a precision of 38 and the same scale as the input
MAX and MIN will have the same precision and the same scale as the input
AVG is always NUMERIC(38,6)
STDev and VAR are always float(53)
SQL = Scarcely Qualifies as a Language
June 18, 2009 at 11:33 pm
Hello Christophe,
We only have two types of weather in Luxembourg - either “sunshine with showers” or “showers with sunshine” 😉
Bonne journée,
John
www.sql.lu
SQL Server Luxembourg User Group
August 28, 2009 at 5:18 am
The example Carl mentions is a classic. I recently saw it on StackOverflow being used as a killer reason for not using Money as a data type.
I agree that there is something one needs to watch out for, but the error is not that of the Money Data type. What the example does is to divide, say $1793.33 by $7328.75 and then multiply the result by $55294.72. Now read that out loud. Does that make sense? No. This is a programming error. You are dividing a quantity by a quantity. If you are unsure, try a different quantity. Divide three sheep by ten sheep and then multipy by fifteen sheep. What result would you expect?
I'd add yet another line to the test, which is
(@Amt3/7328.75)*55294.72 as RealLife,
... which is the way that an accountant would surely express it. As John Marsh has pointed out, if the result of the expression is assigned to a money datatype, it gives the correct result.
In financial database applications, I generally use the money datatype to contain monetary values. It is more for clarity. Excel works quite differently from SQL Server as the 'money' is a display option, but the internal representation does not do the rounding. SQL Server's Money datatype will actually do the rounding to four decimal places of the currency unit, and I think that this was done when reports from the database were actually given 'raw' to the users rather than formatted within an application.
Anyone who has done a lot of tax calculations such as VAT will tell you the problems of calculating a tax that is based on multiplying a monetary value by 1.175. If you are calculating the VAT on each row item in a list of purchases, and displaying the individual results in a column rounded to the nearest penny, then the column total will not be the same as if you were to calculate the VAT on the sum total of the no. of items.
Here is an example of what I mean.
[font="Courier New"]
--firstly we create an 'invoice' table that does the tax calculation
DECLARE @Line TABLE
(
[item] VARCHAR(20),
[Value(Ex VAT)] MONEY,
[VAT] NUMERIC(10, 4) DEFAULT 1.175,
[Value(Inc VAT)] AS CAST([Value(Ex VAT)]*[VAT] AS MONEY)
)
--stock it with some data
INSERT INTO @Line( item, [Value(Ex VAT)] )
SELECT 'barley',$1208.49 UNION ALL SELECT 'worzels',$5609.98 UNION ALL SELECT 'corn',$143.69 UNION ALL SELECT 'oats',$342.89 UNION ALL SELECT 'beet',$100.01 UNION ALL SELECT 'beans',$359.29 UNION ALL SELECT 'silage',$372.91 UNION ALL SELECT 'potatoes',$230.43
--and get an invoice showing the VAT calculations
SELECT item,
[Value(Ex VAT)],
[VAT],
ROUND([Value(Inc VAT)], 2) AS [Value(Inc VAT)]
FROM @Line
UNION ALL
SELECT 'TOTALS',
SUM([Value(Ex VAT)]),
AVG([VAT]),
ROUND(SUM([Value(Inc VAT)]), 2)
FROM @Line
--eek, the total isn't right when you check it with a pocket calculator!
--why is that?
SELECT SUM([Value(Ex VAT)])*AVG([VAT]) AS [total ex vat * vat],
SUM(ROUND([Value(Ex VAT)]*[VAT], 2)) AS [column total]
FROM @Line
--it is because you have to add the rounded figures, not the full figures.
[/font]
Best wishes,
Phil Factor
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply