I’m curious as to whether or not there is a real difference between the
money datatype and something like
decimal(19,4) (which is what money uses internally, I believe).
Decimal type provides more precision to hold intermediate results to get the results you expect.
- It is proprietary, so porting it is a pain. It is one of the many “Sybase Code Museum” features from decades ago. Remember the early versions of UNIX?
- Writing code in dialect when you don’t need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
- It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
- The MONEY data type has rounding errors.
Never ever should you use money it is not precise and it is pure garbage, always use decimal/numeric
run this to see what I mean
DECLARE @mon1 MONEY, @mon2 MONEY, @mon3 MONEY, @mon4 MONEY, @num1 DECIMAL(19,4), @num2 DECIMAL(19,4), @num3 DECIMAL(19,4), @num4 DECIMAL(19,4) SELECT @mon1 = 100, @mon2 = 339, @mon3 = 10000, @num1 = 100, @num2 = 339, @num3 = 10000 SET @mon4 = @mon1/@mon2*@mon3 SET @num4 = @num1/@num2*@num3 SELECT @mon4 AS moneyresult, @num4 AS numericresult money is good as long as you don't need more than 4 decimal digits, and you make sure your scalars - which do not represent money - are
One of my co-workers and I were discussing the difference between the data type MONEY and the data type DECIMAL(19,4). I was rather curious so I spent some time getting it all down on paper.
First the obvious. MONEY takes up 8 bytes and stores the maximum possible values possible for the 8 bytes. 2^64 or -922,337,203,685,477.5808 to 922,337,203,685,477.5807. DECIMAL(19,4) will store any value that will fit into MONEY and then some, but it takes up 9 bytes. A DECIMAL(19,4) can hold a range of -999,999,999,999,999.9999 to 999,999,999,999,999.9999. This is actually considerably less than 2^72 (8bits * 9bytes) or -236,118,324,143,482,260.6848 to 236,118,324,143,482,260.6847 that actually could be stored in 9 bytes.
Next something interesting to try
With an output of
If you look at the Length, Precision and Scale you will notice that they change for any of the computed columns that use the DECIMAL column. This is because if you (for example) multiply a DECIMAL(4,2) times another DECIMAL(4,2) you end up with a DECIMAL(8,4). It stops at Precision 38 because that is the maximum size of a DECIMAL data type. The MONEY data type however does not change when multiplied by itself.
So the major difference is that Money data types are smaller but have a fixed size. Decimal on the other hand takes up more space and when multiplying the size, precision, and scale are going to change. Personally that means that I’m going to story invoice amounts (for example) as Money (or even SmallMoney where I can) and tax rate as a Decimal.