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.

Advertisements

About Karl Beran

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: