Posted by David Portas on 09/30/05 01:52

> Are you channelling Celko? :-)

Nah, I'm a total pussycat!

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

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

--
David Portas
SQL Server MVP
--

"Simon Hayes" <sql@hayes.ch> wrote in message
news:433c6523$1_1@news.bluewin.ch...
> David Portas wrote:
>>>create table a
>>> (account int
>>> ,balance_date datetime
>>> ,balance money)
>>
>>
>>
>> Why are all your columns nullable? What is/are the key/s of this table?
>> Are you aware that MONEY will give you imprecisely rounded results when
>> you multiply or divide?
>>
>> Try:
>>
>> SELECT account, balance_date, balance
>> FROM a AS T
>> WHERE balance_date =
>> (SELECT MAX(balance_date)
>> FROM a
>> WHERE account = T.account) ;
>>
>
> David,
>
> Are you channelling Celko? :-) 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.
>
> 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).
> Ignoring the nonsense with currency symbols which MSSQL allows, and
> forgetting about currencies like the old Turkish Lira (where dec(24,4) or
> more might be needed in certain industries), why would there be any reason
> to believe that results will be imprecise?
>
> Simon

[Back to original message]