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:
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
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
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:
- Always use a decimal(19,4) data type instead of money – it only requires 9 bytes instead of 8 for money.
- 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.