How to store money in SQL Server

 In SQL, T-SQL

Today I would like to present another intriguing and challenging topic in SQL. When constructing databases or data warehouses it may be required to store financial figures like amounts in currency or FX rates in SQL Server.

Let’s take a look at tools in our disposal. Microsoft provides us with ‘Exact Numerics’ and ‘Approximate Numerics’.

Examples:

 

approximatenumbers exactnumbers

 

But, because we will need to store financial figures we will get values with variable decimals. So to start, we can use:

  • float / real (different range, storage)
  • decimal / numeric (almost the same, functional equivalence)
  • money / smallmoney

The main problem with ‘float’ and ‘real’ is that they are approximate numerics, so they don’t store exact values. Example:

Result:

float

Float has a not-known, non-deterministic precision. So you should never use float or real for storing money in SQL Server.

 

Money vs Decimal

Ok, let’s compare ‘money’ vs ‘decimal’.

Let’s assume that we will use decimal (19,4). It will allow us to store maximum 19 of total numbers and 4 decimal digits – in most cases it will be fine. It will be stored in 9 bytes according to storage type.

storagevbytesdecimal

 

 

 

 

 

On the other hand money data type is 8 bytes. Money’s range is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, so decimal(19,4) can store any value that fits money. There is also smallmoney data type available if you would need it, but it’srange is pretty small (- 214,748.3648 to 214,748.3647).

 

When you think about situation in which money data type can be used in SQL you will probably come to the conculsion that you can:

  • add / subtract (for example to get sum of expenses)
  • divide (for example to get % in KPI’s)

Let’s take a look at this short example, that I have prepared

And the results are:

moneydecimal

 

Money and decimal are useful in case of values and sums. However money is not a correct data type in case of division (The result is 0,00009 so it should be rounded to 0,0001).

 

To sum up:

If you have an OLTP-like case and you store values like 1000.24 USD I would suggest storing values in money or smallmoney data types.

If you have an OLAP-like case where division or multiplication operations might occur I would suggest going with decimal data type.

 

Mateusz
Creator & author of blog.
Recommended Posts
Showing 3 comments
  • Dominik

    Good and usefull article. Keep going like that.

  • Vickey Acero

    I have to show thanks to the writer for rescuing me from such a matter. After browsing throughout the world wide web and getting principles that were not helpful, I figured my entire life was well over. Living devoid of the approaches to the problems you’ve solved all through your good article content is a serious case, and ones that could have negatively affected my career if I hadn’t come across the website. Your good knowledge and kindness in playing with the whole thing was useful. I don’t know what I would’ve done if I hadn’t come upon such a step like this. I’m able to at this time look ahead to my future. Thanks so much for your high quality and result oriented help. I will not be reluctant to propose the sites to anybody who requires guidance about this issue.

  • JacquesStant

    I see your site needs some unique articles. Writing manually
    is time consuming, but there is solution for this. Just search
    for: Masquro’s strategies

Contact Me

You can contact me with this form. :)

Not readable? Change text.