David Portas wrote:
>>Are you channelling Celko? :-)
>
>
> Nah, I'm a total pussycat!
>
>

Hmm, no comment... :-)


>>Whether or not the columns are NULL or NOT NULL depends on
>>ANSI_NULL_DFLT_ON/OFF - admittedly, they will be NULL by default most of
>>the time.
>
>
> True, but the purpose of posting DDL is to define the problem more clearly.
> Rong's DDL was about as clear as mud - and not much more useful.
>
>

Agreed - I was certainly splitting hairs there. If you want other people
to reproduce your results, then of course you need a script which works
the same way everywhere. Or at least as far as possible; if you really
want reproducible DDL then you need the COLLATE clause with every
character data type, and even then if your client doesn't behave the
same way as the OP's, you may find it tough to compare (in addition to
the numerous issues which I didn't even think of). Sounds like I'm
splitting hairs again...


>>As for money, what data type should an amount of money be; money in MSSQL
>>is is essentially decimal(19,4), which is a precise data type (and
>>specifically recommended by BOL for cases where precision is required).
>
>
> Incorrect, false and wrong! MONEY is definitively not even near equivalent
> to DECIMAL(19,4). My professional advice is always that MONEY is unsuitable
> for monetary amounts. See the example below for why. Now it's a fact that
> DECIMAL isn't without its own issues because the coercian rules for DECIMAL
> are not documented and not always well understood, but there is no excuse at
> all for MONEY or SMALLMONEY.
>
> DECLA RE
> @mon1 MONEY,
> @mon2 MONEY,
> @mon3 MONEY,
> @mon4 MONEY,
> @num1 DECIMAL(19,4),
> @num2 DECIMAL(19,4),
> @num3 DECIMAL(19,4),
> @num4 DECIMAL(19,4)
>
> SELECT
> @mon1 = 100, @mon2 = 339, @mon3 = 10000,
> @num1 = 100, @num2 = 339, @num3 = 10000
>
> SET @mon4 = @mon1/@mon2*@mon3
> SET @num4 = @num1/@num2*@num3
>
> SELECT @mon4 AS money_result,
> @num4 AS numeric_result
>
>
> Result:
>
> money_result numeric_result
> --------------------- ---------------------
> 2949.0000 2949.8525
>
>
> (1 row(s) affected)
>

OK, this is where it gets interesting. My experience is that you do the
"right thing", so you go to the accounting department and ask for their
data storage and rounding rules, based on GAAP and EU regulations. The
answer is normally a resounding "huh?". So you essentially cover your
backside by choosing 'money', on the grounds that MS recommended it (and
you document the fact that Accounting had no objections). If and when
you have rounding errors, then you blame the accountants, quote BOL and
give a theoretical example, as you and I have just done between us.

This may seem excessively cynical, but I have to admit that I have no
experience at all of a company where anyone cares about a difference as
small as the one you've shown. Which is not to say that it's not
important to some people, simply that it's never been important to me or
my clients, in the context of the applications that I've worked on.

I suspect that this is a long-winded way of saying that you're right and
I'm wrong, but this is one issue in data modelling where I've seen very
little solid information. You've said that 'money' isn't appropriate for
monetary amounts, and it's not equivalent to dec(19,4); fair enough, but
what is the appropriate data type? Another good example is Celko's
mystical natural primary key for human beings, which he seems to think
is an SSN, despite the fact that most of the world's never heard of one...

Simon

Use DECIMAL / NUMERIC for monetary amounts. Why would you need anything
else?

I haven't seen a recommendation from Microsoft to use MONEY for
monetary amounts. Maybe you could post a reference if you know of one
because I'd like to send some feedback telling them to change it. What
they DO say is "Monetary data types for representing monetary or
currency values", which is a statement not a recommendation.

Knowing the rounding issues I think I'd have to be either careless,
malicious or under written instructions from my boss to use MONEY in
permanent tables in a production database. It does have some potential
uses for formatting and converting values if you need to do such things
in the database.

--
David Portas
SQL Server MVP
--