Friday, August 28, 2015

Decimal vs Money. What consumes less space?

I love to use SMALLMONEY data type. it holds decimal amounts up to about 200K and have 4 digits in the scale and for all these features it consumes only four bytes, same as a regular integer.

But "WHAT IF?"....

What if your system will hit a value over the magic number?
What if you specified it for a "Price" columns and inflation in your country has driven prices to the roof?

Would you use MONEY data type instead?

Yes, you can, but in this case your column will consume four additional bytes. If your table is small it it nothing to worry about, but if it is huge.

Here are the data type limits for MONEY data types:


Let's try to use DECIMAL data type instead.
Here are the data type limits for DECIMAL/NUMERIC data types:

As you can see, SMALLMONEY data type in translation to DECIMAL is already goes to Precision of 10 and accordingly to the second line will consume 9 bytes instead of 4. That is even more than MONEY.

Is it the end?

Actually not.

You will be surprised, that DECIMAL value of "429496.7295", which can not fit into SMALLMONEY still uses 5 bytes!

Now, would say your currency has only 2 digits after decimal point and you do not need four digits, which SMALLMONEY provides.
Then you can go beyond and your "5 bytes Limit" provides ability to store a number up to 42,949,672.95!

42 Millions!!!!

Would say you need a very good precision for financial operations and still need four digits.

You have a contradiction: You need to keep size as small as possible, but it might cause an issue.
In that case you can use data type from the second decimal line "DECIMAL(19,4)"!

Even though MSDN says it consumes 9 bytes it still consumes only 5 until it reaches magic number of  "429,496.7295".

Here is the proof:
DECLARE @d DECIMAL(19,4) = 429496.7295
SELECT @d, DATALENGTH(@d);





So, if you plan to have mostly numbers below of SMALLMONEY limit, but sometimes little bit higher than it might worth to use "DECIMAL(19,4)" instead. Yes, you will consume 25% more space, but you will be ensured from any surprises and that is much smaller insurance than 100% of MONEY data type


No comments:

Post a Comment