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
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

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

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

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Ronald S. Cook" <rc***@westinis.comwrote in message
news:Ox**************@TK2MSFTNGP04.phx.gbl...

Please disregard this -thx
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>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

[Rad [Visual C# MVP]]

On Sat, 14 Apr 2007 10:20:31 -0400, Nicholas Paldino [.NET/C# MVP] wrote:

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?

--
Bits.Bytes
http://bytes.thinkersroom.com

[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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Rad [Visual C# MVP]" <no****@nospam.comwrote in message
news:1c****************@thinkersroom.com...

On Sat, 14 Apr 2007 10:20:31 -0400, Nicholas Paldino [.NET/C# MVP] wrote:

>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?

--
Bits.Bytes
http://bytes.thinkersroom.com