# Implicit or Explicit – you decide!

Implicit data type conversion is there to help us and trip us up at the same time. The two biggest culprits are using money data types instead of decimal and storing percentages as whole numbers rather than decimals (ie 12.5% as 12.5 rather than 0.125). I have to thank Matt for uncovering this gem.

Say we have a SalesAmount and want to work out the discount amount of a 2.95% discount, quite logically we might do something like:

```USE AdventureWorksDW2008

DECLARE @DiscountAsReal         money
DECLARE @DiscountAsNumerator    money

SET @DiscountAsReal         = 0.0295
SET @DiscountAsNumerator    = 2.95

SELECT TOP 10
F.SalesAmount
,@DiscountAsReal                                AS DiscountAsReal
,@DiscountAsNumerator                           AS DiscountAsNumerator
,F.SalesAmount * @DiscountAsReal                AS DiscountAsRealAmount
,F.SalesAmount * @DiscountAsNumerator / 100     AS DiscountAsNumeratorAmount  FROM
FactResellerSales F```

and then get the following results:

 SalesAmount DiscountAsReal DiscountAsNumerator DiscountAsRealAmount DiscountAsNumeratorAmount 2024.994 0.0295 2.95 59.7373 59.7373 6074.982 0.0295 2.95 179.212 179.2119 2024.994 0.0295 2.95 59.7373 59.7373 2039.994 0.0295 2.95 60.1798 60.1798 2039.994 0.0295 2.95 60.1798 60.1798 4079.988 0.0295 2.95 120.3596 120.3596 2039.994 0.0295 2.95 60.1798 60.1798 86.5212 0.0295 2.95 2.5524 2.5523 28.8404 0.0295 2.95 0.8508 0.8507 34.2 0.0295 2.95 1.0089 1.0089

The problem is the numbers in red don’t match and logically they should. You could say that such a small amount doesn’t really matter, but when multiple discounts are applied then thousands or millions of transactions are aggregated you get into a tangle trying to explain to accountants why your numbers that should be simple multiplication don’t reconcile with theirs.

So what is the cause of all this? Implicit data type conversion – because the money data type only contains four decimal places it starts doing some unusual things when division is involved.

One way to see this in action is comparing the output of this first query with the second:

```DECLARE @d as decimal(5,4)
DECLARE @m as money

SET @d = 1.0
SET @m = 1.0

SELECT
@d                AS dec1
,@d * @d          AS dec2
,@d * @d * @d     AS dec3
,@m               AS mon1
,@m * @m          AS mon2
,@m * @m * @m     AS mon3```
 dec1 dec2 dec3 mon1 mon2 mon3 1.0000 1.00000000 1.000000000000 1.00 1.00 1.00
Notice how as the decimals are multiplied together the number of decimal places increases, but the same does not apply for money. When we change the values from 1.0 to 1.0009 and change the second multiplication to a division we get some strange results highlighted in red below:
```DECLARE @d as decimal(5,4)
DECLARE @m as money

SET @d = 1.0009
SET @m = 1.0009

SELECT
@d               AS dec1
,@d * @d          AS dec2
,@d * @d / @d     AS dec3
,@m               AS mon1
,@m * @m          AS mon2
,@m * @m / @m     AS mon3```
 dec1 dec2 dec3 mon1 mon2 mon3 1.0009 1.00180081 1.00090000000000 1.0009 1.0018 1.0008

There’s a quick fix in which can be applied to the first example:

Instead of dividing by 100 you divide by 100.00 , I believe this forces an implicit conversion of all values to a decimal

However there are two things you can do to avoid this malarkey altogether:

1. Always use a decimal(19,4) data type instead of money – it only requires 9 bytes instead of 8 for money.
2. Always store percentages as their decimal equivalent (ie 0.125 instead of 12.5)

Some may argue that money is useful as it doesn’t display any of the trailing zeros. I say you have format strings in your cube or presentation tool to handle that. Also money isn’t an ANSI data type.

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on March 19, 2010, in SQL. Bookmark the permalink. 1 Comment.

1. Jes Borland ( blog ) has written a relevant post about implicit data conversion with examples showing query execution plans.

Like