Which SQL Server field type is best for storing price values?
I am wondering what's the best type for a price field in SQL Server for a shop-like structure?
Looking at this overview 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 )
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 web-shops 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
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).