Which fieldtype is best for storing PRICE values?
- by BerggreenDK
Hi there I am wondering whats the best "price field" in MSSQL for a shoplike structure?
Looking at this overview: http://www.teratrax.com/sql_guide/data_types/sql_server_data_types.html
We have datatypes 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 pricevalues 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 dont 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 regionalsettings 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?
Whats does your experience tell?
