Should you choose the MONEY or DECIMAL x y datatypes in SQL Server

Question

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    I m aware that money is specific to SQL Server  I want to know if there is a compelling reason to choose one over the other  most SQL Server samples  e g  the AdventureWorks database  use money and not decimal for things like price information     Should I just continue to use the money datatype  or is there a benefit to using decimal instead  Money is fewer characters to type  but that s not a valid reason

User · Answer

As a counter point to the general thrust of the other answers. See The Many Benefits of Money…Data Type! in SQLCAT's Guide to Relational Engine

Specifically I would point out the following

Working on customer implementations, we found some interesting performance numbers concerning the money data type. For example, when Analysis Services was set to the currency data type (from double) to match the SQL Server money data type, there was a 13% improvement in processing speed (rows/sec). To get faster performance within SQL Server Integration Services (SSIS) to load 1.18 TB in under thirty minutes, as noted in SSIS 2008 - world record ETL performance, it was observed that changing the four decimal(9,2) columns with a size of 5 bytes in the TPC-H LINEITEM table to money (8 bytes) improved bulk inserting speed by 20% ... The reason for the performance improvement is because of SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 - world record ETL performance test using Kernrate; the protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex data type needs additional parsing and CPU cycles to handle than a fixed-width type.

So the answer to the question is "it depends". You need to be more careful with certain arithmetical operations to preserve precision but you may find that performance considerations make this worthwhile.

User · Answer

SQLMenace said money is inexact  But you don t multiply divide money by money  How much is 3 dollars times 50 cents  150 dollarcents  You multiply divide money by scalars  which should be decimal   DECLARE  mon1 MONEY   mon4 MONEY   num1 DECIMAL 19 4    num2 DECIMAL 19 4    num3 DECIMAL 19 4    num4 DECIMAL 19 4   SELECT  mon1   100   num1   100   num2   339   num3   10000  SET  mon4    mon1  num2  num3 SET  num4    num1  num2  num3  SELECT  mon4 AS moneyresult   num4 AS numericresult   Results in the correct result   moneyresult           numericresult --------------------- --------------------------------------- 2949 8525             2949 8525  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 decimals

User · Answer

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   Output  2949 0000 2949 8525  To some of the people who said that you don t divide money by money   Here is one of my queries to calculate correlations  and changing that to money gives wrong results   select t1 index id t2 index id  avg t1 monret t2 monret      - avg t1 monret    avg t2 monret                   sqrt avg square t1 monret   - square avg t1 monret                   sqrt avg square t2 monret   - square avg t2 monret       current timestamp  MaxDate             from Table1 t1  join Table1 t2  on t1 Date   traDate             group by t1 index id t2 index id

User · Answer

All the previous posts bring valid points  but some don t answer the question precisely   The question is  Why would someone prefer money when we already know it is a less precise data type and can cause errors if used in complex calculations   You use money when you won t make complex calculations and can trade this precision for other needs   For example  when you don t have to make those calculations  and need to import data from valid currency text strings  This automatic conversion works only with MONEY data type   SELECT CONVERT MONEY    1 000 68     I know you can make your own import routine  But sometimes you don t want to recreate a import routine with worldwide specific locale formats   Another example  when you don t have to make those calculations  you need just to store a value  and need to save 1 byte  money takes 8 bytes and decimal 19 4  takes 9 bytes   In some applications  fast CPU  big RAM  slow IO   like just reading huge amount of data  this can be faster too

User · Answer

I realise that WayneM has stated he knows that money is specific to SQL Server  However  he is asking if there are any reasons to use money over decimal or vice versa and I think one obvious reason still ought to be stated and that is using decimal means it s one less thing to worry about if you ever have to change your DBMS - which can happen   Make your systems as flexible as possible

User · Answer

I found a reason about using decimal over money in accuracy subject   DECLARE  dOne   DECIMAL 19 4            dThree DECIMAL 19 4            mOne   MONEY           mThree MONEY           fOne   FLOAT           fThree FLOAT   SELECT  dOne     1           dThree   3               mOne     1           mThree   3               fOne     1           fThree   3   SELECT   dOne  dThree   dThree AS DecimalResult            mOne  mThree   mThree AS MoneyResult            fOne  fThree   fThree AS FloatResult     DecimalResult   1 000000  MoneyResult   0 9999  FloatResult    1  Just test it and make your decision

User · Answer

Everything is dangerous if you don t know what you are doing  Even high-precision decimal types can t save the day   declare  num1 numeric 38 22  declare  num2 numeric 38 22  set  num1    0000006 set  num2   1 0 select  num1    num2   1000000   1 000000   lt - Should be 0 6000000    The money types are integers  The text representations of smallmoney and decimal 10 4  may look alike  but that doesn t make them interchangeable   Do you cringe when you see dates stored as varchar 10    This is the same thing   Behind the scenes  money smallmoney are just a bigint int  The decimal point in the text representation of money is visual fluff  just like the dashes in a yyyy-mm-dd date   SQL doesn t actually store those internally   Regarding decimal vs money  pick whatever is appropriate for your needs   The money types exist because storing accounting values as integer multiples of 1 10000th of unit is very common   Also  if you are dealing with actual money and calculations beyond simple addition and subtraction  you shouldn t be doing that at the database level   Do it at the application level with a library that supports Banker s Rounding  IEEE 754

User · Answer

I just saw this blog entry  Money vs  Decimal in SQL Server   Which basically says that money has a precision issue     declare  m money declare  d decimal 9 2   set  m   19 34 set  d   19 34  select   m 1000  1000 select   d 1000  1000   For the money type  you will get 19 30 instead of 19 34  I am not sure if there is an application scenario that divides money into 1000 parts for calculation  but this example does expose some limitations

User · Answer

I want to give a different view of MONEY vs  NUMERICAL  largely based my own expertise and experience    My point of view here is MONEY  because I have worked with it for a considerable long time and never really used NUMERICAL much     MONEY Pro    Native Data Type  It uses a native data type  integer  as the same as a CPU register  32 or 64 bit   so the calculation doesn t need unnecessary overhead so it s smaller and faster    MONEY needs 8 bytes and NUMERICAL 19  4  needs 9 bytes  12 5  bigger      MONEY is faster as long as it is used for it was meant to be  as money   How fast  My simple SUM test on 1 million data shows that MONEY is 275 ms and NUMERIC 517 ms    That is almost twice as fast    Why SUM test  See next Pro point Best for Money  MONEY is best for storing money and do operations  for example  in accounting  A single report can run millions of additions  SUM  and a few multiplications after the SUM operation is done  For very big accounting applications it is almost twice as fast  and it is extremely significant    Low Precision of Money  Money in real life doesn t need to be very precise  I mean  many people may care about 1 cent USD  but how about 0 01 cent USD  In fact  in my country  banks no longer care about cents  digit after decimal comma   I don t know about US bank or other country      MONEY Con    Limited Precision  MONEY only has four digits  after the comma  precision  so it has to be converted before doing operations such as division    But then again money doesn t need to be so precise and is meant to be used as money  not just a number      But    Big  but here is even your application involved real-money  but do not use it in lots of SUM operations  like in accounting  If you use lots of divisions and multiplications instead then you should not use MONEY

User · Answer

We ve just come across a very similar issue and I m now very much a  1 for never using Money except in top level presentation  We have multiple tables  effectively a sales voucher and sales invoice  each of which contains one or more Money fields for historical reasons  and we need to perform a pro-rata calculation to work out how much of the total invoice Tax is relevant to each line on the sales voucher  Our calculation is   vat proportion   total invoice vat x  voucher line value   total invoice value    This results in a real world money   money calculation which causes scale errors on the division part  which then multiplies up into an incorrect vat proportion  When these values are subsequently added  we end up with a sum of the vat proportions which do not add up to the total invoice value  Had either of the values in the brackets been a decimal  I m about to cast one of them as such  the vat proportion would be correct   When the brackets weren t there originally this used to work  I guess because of the larger values involved  it was effectively simulating a higher scale  We added the brackets because it was doing the multiplication first  which was in some rare cases blowing the precision available for the calculation  but this has now caused this much more common error

User · Answer

You shouldn t use money when you need to do multiplications   divisions on the value   Money is stored in the same way an integer is stored  whereas decimal is stored as a decimal point and decimal digits   This means that money will drop accuracy in most cases  while decimal will only do so when converted back to its original scale   Money is fixed point  so its scale doesn t change during calculations   However because it is fixed point when it gets printed as a decimal string  as opposed to as a fixed position in a base 2 string   values up to the scale of 4 are represented exactly   So for addition and subtraction  money is fine   A decimal is represented in base 10 internally  and thus the position of the decimal point is also based on the base 10 number   Which makes its fractional part represent its value exactly  just like with money   The difference is that intermediate values of decimal can maintain precision up to 38 digits   With a floating point number  the value is stored in binary as if it were an integer  and the decimal  or binary  ahem  point s position is relative to the bits representing the number   Because it is a binary decimal point  base 10 numbers lose precision right after the decimal point  1 5th  or 0 2  cannot be represented precisely in this way   Neither money nor decimal suffer from this limitation   It is easy enough to convert money to decimal  perform the calculations  and then store the resulting value back into a money field or variable   From my POV  I want stuff that happens to numbers to just happen without having to give too much thought to them   If all calculations are going to get converted to decimal  then to me I d just want to use decimal   I d save the money field for display purposes   Size-wise I don t see enough of a difference to change my mind   Money takes 4 - 8 bytes  whereas decimal can be 5  9  13  and 17   The 9 bytes can cover the entire range that the 8 bytes of money can   Index-wise  comparing and searching should be comparable

User · Answer

Well  I like MONEY  It s a byte cheaper than DECIMAL  and the computations perform quicker because  under the covers  addition and subtraction operations are essentially integer operations    SQLMenace s example mdash which is a great warning for the unaware mdash could equally be applied to INTegers  where the result would be zero   But that s no reason not to use integers mdash where appropriate   So  it s perfectly  safe  and appropriate to use MONEY when what you are dealing with is MONEY and use it according to mathematical rules that it follows  same as INTeger    Would it have been better if SQL Server promoted division and multiplication of MONEY s into DECIMALs  or FLOATs   mdash possibly  but they didn t choose to do this  nor did they choose to promote INTegers to FLOATs when dividing them   MONEY has no precision issue  that DECIMALs get to have a larger intermediate type used during calculations is just a  feature  of using that type  and I m not actually sure how far that  feature  extends    To answer the specific question  a  compelling reason    Well  if you want absolute maximum performance in a SUM x  where x could be either DECIMAL or MONEY  then MONEY will have an edge   Also  don t forget it s smaller cousin  SMALLMONEY mdash just 4 bytes  but it does max out at 214 748 3647 - which is pretty small for money mdash and so is not often a good fit   To prove the point around using larger intermediate types  if you assign the intermediate explicitly to a variable  DECIMAL suffers the same problem   declare  a decimal 19 4  declare  b decimal 19 4  declare  c decimal 19 4  declare  d decimal 19 4   select  a   100   b   339   c   10000  set  d    a  b  set  d    d  c  select  d   Produces 2950 0000  okay  so at least DECIMAL rounded rather than MONEY truncated mdash same as an integer would

[sql-server] Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

Examples related to sql-server

Examples related to types