Hello world! (and why you should use the Decimal data type and not Money in SQL Server)

Hi!  This blog is called “The Order of SQL” as both a reference to the community of SQL users I hope to cultivate here and to an upcoming e-book I am working on that explains in simple terms exactly how a SQL query is processed and in what order (no, it is NOT all processed simultaneously).

I will try to stick to ANSI SQL as close as possible, but since my focus professionally is on SQL Server , I may occasionally add in a tidbit or two specific to that platform.  Hence, today, we discuss an important “gotcha” in SQL Server around the currency datatype.

Check this example out:

declare @m money
declare @d decimal(9,2)

set @m = 19.34
set @d = 19.34

select (@m/1000)*1000 as money, (@d/1000)*1000 as decimal

RESULTS:
money     decimal
19.30       19.3400000

It should be obvious, that if we start with ANY number (19.34 in this case), and then simply divide by ANY number (1000 in this case), and then multiply by the SAME number we divided with (1000), we should always end up with the number we started with.
So, what happened with the “money” column?  Where did the .04 go?

 

The answer is related to how the money (aka currency) data type is stored: it has a “scale” of only 4.  This means that it can only store 4 decimals after the decimal point.

 

So, here is what happened:
19.34/1000 = .01934  BUT we only have 4 decimal places, so, this is truncated to .0193.
THEN, .0193 * 1000 = 19.30!

 

Some of you may now being saying, “Wait!  The decimal variable has even LESS scale (2).  How did it maintain all the decimals?!”

 

This is apparently due to the fact that although the variable @d can only store 2 decimals, the intermediate results of a computation involving that variable can indeed be stored beyond that limitation.  The problem with the currency data types is that SQL Server apparently chooses to store those intermediate results at the same scale as the money variable itself, hence the truncation of the 4 from the end of .01934.

 

The lesson?  Just use Decimal (9,2) for currency — it only  takes 5 bytes of storage.  I have yet to find a reason to extend that scale beyond 2, although I’ve seen some people do it (e.g., Decimal (10,4)).  If you know of an advantage or reason to do that – please let me know in the comments below!

 

Thanks and keep checking back for my ebook on the “order of SQL” – it will be good — I promise! — and most likely will be free at first, as I hope to get YOUR help in making it better before I sell it – see ya soon!
Scott
Advertisements

About scottedwards2000

data guy
This entry was posted in Uncategorized. Bookmark the permalink.

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