Which fieldtype is best for storing PRICE values?

Question

Hi there I am wondering whats the best "price field" in SQL Server for a shoplike structure?

Looking at this overview: http://www.teratrax.com/sql_guide/data_types/sql_server_data_types.html

We have data types called money, smallmoney, then we have decimal/numeric and lastly float and real

Name, memory/disk-usage and value ranges:

• Money: 8 bytes (values: -922,337,203,685,477.5808 to +922,337,203,685,477.5807)
• Smallmoney: 4 bytes (values: -214,748.3648 to +214,748.3647)
• Decimal: 9 [default, min. 5] bytes (values: -10^38 +1 to 10^38 -1 )
• Float: 8 bytes (values: -1.79E+308 to 1.79E+308 )
• Real: 4 bytes (values: -3.40E+38 to 3.40E+38 )

My question is: is it really wise to store price values in those types?

what about eg. INT?

• Int: 4 bytes (values: -2,147,483,648 to 2,147,483,647)

Lets say a shop uses dollars, they have cents, but I don't see prices being $49.2142342 so the use of a lot of decimals showing cents seems waste of SQL bandwidth. Secondly, most shops wouldn't show any prices near 200.000.000 (not in normal webshops at least... unless someone is trying to sell me a famous tower in Paris)

So why not go for an int?

An int is fast, its only 4 bytes and you can easily make decimals, by saving values in cents instead of dollars and then divide when you present the values.

The other approach would be to use smallmoney which is 4 bytes too, but this will require the math part of the CPU to do the calc, where as Int is integer power... on the downside you will need to divide every single outcome.

Are there any "currency" related problems with regional settings when using smallmoney/money fields? what will these transfer too in C#/.NET ?

Any pros/cons? Go for integer prices or smallmoney or some other?

What does your experience tell?

Answer 1

Use the Money datatype if you are storing money (unless modelling huge amounts of money like the national debt) - it avoids precision/rounding issues.

The Many Benefits of Money…Data Type!

Answer 2

Personally, I'd use smallmoney or money to store shop prices.

Using int adds complexity elsewhere.

And 200 million is perfectly valid price in Korean Won or Indonesian Rupees too...

Answer 3

If you're absolutely sure your numbers will always stay within the range of smallmoney, use that and you can save a few bytes. Otherwise, I would use money. But remember, storage is cheap these days. The extra 4 bytes over 100 million records is still less than half a GB. As @marc_s points out, however, using smallmoney if you can will reduce the memory footprint of SQL server.

Long story short, if you can get away with smallmoney, do. If you think you might go over the max, use money.

But, do not use a floating-decimal type or you will get rounding issues and will start losing or gaining random cents, unless you deal with them properly.

My argument against using int: Why reinvent the wheel by storing an int and then having to remember to divide by 100 (10000) to retrieve the value and multiply back when you go to store the value. My understanding is the money types use an int or long as the underlying storage type anyway.

As far as the corresponding data type in .NET, it will be decimal (which will also avoid rounding issues in your C# code).

Answer 4

SQL data types money and smallmoney both resolve to c# decimal type:

http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlmoney(v=VS.71).aspx

So I'm thinking that you might as well go for decimal. Personally I've been using double all my life working in the financial industry and haven't experienced performance issues, etc. Actually, I've found that for certain calculations, etc., having a larger data type allows for higher degree of accuracy.

Answer 5

I would go for the Money datatype. Invididually you may not exceed the value in Smallmoney, but it would be easy for multiple items to exceed it.

Answer 6

USE NUMERIC / DECIMAL. Avoid MONEY / SMALLMONEY. Here's an example of why. Sooner or later the MONEY / SMALLMONEY types will likely let you down due to rounding errors. The money types are completely redundant and achieve nothing useful - a currency amount being just another decimal number like any other.

Lastly, the MONEY / SMALLMONEY types are proprietary to Microsoft. NUMERIC / DECIMAL are part of the SQL standard. They are used, recognised and understood by more people and are supported by most DBMSs and other software.

Answer 7

In my pawnshop app, the pawnshop operators lend from $5.00 to $10,000.00 When they calculate the loan amount they round it to the nearest dollar in order to avoid dealing with cents (the same applies for interest payments). When the loan amount is above $50.00 they will round it to the nearest $5.00 (i.e. $50, $55, $60 ...), again to minimize running out of dollar bills. Therefore, I use DECIMAL(7,2) for transaction.calculated_loan_amount and DECIMAL(5,0) for transaction.loan_amount. The app calculates the loan amount to the penny and places that amount in loan_amount where it gets rounded to the nearest dollar when below $50 or to the nearest $5.00 when greater.