Which SQL Server field type is best for storing price values?

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).

Leave a Comment