Should I use SQL data type money or decimal given .NET datatype is decimal?

[Ronald S. Cook]

We're designing the data model for a project. The app will be in .NET, the
database will be in SQL Server 2005.

I'm a little confused on type conversion between the two and which I should
choose.

I found this good comparison chart:
http://msdn2.microsoft.com/en-us/library/ms131092.aspx

But for monetary amounts, it looks like decimal is how .NET is going to
store the value. So then, should I go with decimal in the database for a
nice match? But then it looks like money would be a good choice.

How do I know which I should ideally choose?

It would be nice if all the types exactly matched up between the platforms.

Thanks,
Ron

 

[Ronald S. Cook]

Please disregard this -thx

 

[Nicholas Paldino [.NET/C# MVP]]

Ronald,

Why disregard? Care to say what conclusions you came to?

Personally, I think that if you are going to store money values in the
database, then money is fine. However, if the table you are storing values
on is an intermediate table which will store values performed on money and
then have more calculations performed on those results, then you should use
decimal with a much larger scale.

For example, say you have a Product table, with a Price field which is
the price for the Product. This price should be Money.

Now, if you had an intermediate table, with an average of how much money
was spent per customer, then that field should be decimal, with a larger
scale. The reason for this is that if you use those averages as inputs into
calculations later, then you don't want a truncation of values before the
money values go into the calc.

But then again, maybe you do, it all depends on what the accuracy you
need. Personally, I like to have as many places as possible being stored
(given practical limitations) and used for my intermediary calcs and then
perform the final rounding when the results are presented back to the user.

 

[Rad [Visual C# MVP]]

Ronald,

Why disregard? Care to say what conclusions you came to?

Personally, I think that if you are going to store money values in the
database, then money is fine. However, if the table you are storing values
on is an intermediate table which will store values performed on money and
then have more calculations performed on those results, then you should use
decimal with a much larger scale.

For example, say you have a Product table, with a Price field which is
the price for the Product. This price should be Money.

Now, if you had an intermediate table, with an average of how much money
was spent per customer, then that field should be decimal, with a larger
scale. The reason for this is that if you use those averages as inputs into
calculations later, then you don't want a truncation of values before the
money values go into the calc.

But then again, maybe you do, it all depends on what the accuracy you
need. Personally, I like to have as many places as possible being stored
(given practical limitations) and used for my intermediary calcs and then
perform the final rounding when the results are presented back to the user.

I agree with you about the accuracy but what benefits can you derive seeing
as money only makes sense to 2-3 decimal points?

 

[Nicholas Paldino [.NET/C# MVP]]

Rad,

If you are storing intermediate calculation values in a table which are
monetary values, then you might want that accuracy when using those
intermediary values in further calculations. It wouldn't be until the end
where you want to apply the rounding.