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