Pros And Cons Of Money DataType

Hi Friends!

When I started googling for different datatype problems, a lot of interesting things came up, and money is one of the most popular ones, and we should always be thinking before we use money datatype.

The following is the script and output for money datatype.

SQL SERVER

We can see money with float operation does not show the required output. Now, you may say that you want a comparison between decimal and money.

I found the comparison on the following link and copied some data as well.

Money vs. Decimal vs. Float Decision Flowchart

Below is a high-level decision flowchart to help you decide which datatype you should use. Note that this is a generalization that may not be applicable to all situations. For a more in-depth understanding, you can always refer to Donald Knuth’s "The Art of Computer Programming – Volume 1".

SQL Server

In that link, I found a lot of good features about money datatype.

Money (Datatype) Internals

The reason for the performance improvement is SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 – world record ETL performance test using Kernrate protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex datatype needs additional parsing and CPU cycles to handle than a fixed-width type.

Let’s compare the different datatypes that are typically used with money (datatypes).

SQL Server

The key here is that the money datatype is a simple fixed-length integer-based value type with a fixed decimal point. Composed of an 8-byte signed integer (note that small money is a single 4-byte integer) with the 4-byte CPU alignment, it is more efficient to process than its decimal and floating point counterparts. The other side of the coin is that floating points (but not decimal) can be more quickly calculated in the floating point unit of your CPU than money. However, bear in mind the precision issues of float as noted above.

Saving (Space for) Your Money!

In the context of SQL Server data compression, the money and small money data types tend to compress well when the absolute value is low (e.g., values between -0.0128 and 0.0127 compress to 1 byte, while values between -3.2768 and 3.2767 compress to 2 bytes). It is the absolute value that matters for compression, not the number of significant digits; both 1,000,000 and 1,234,567.8901 will take 5 bytes compressed. On the other hand, decimal will compress better when there are fewer significant digits. For example, both 1,000,000 and .0001 will compress to 2 bytes, but 1,234,567.8901 will take several more bytes because it has more significant digits.

Summary for the feature of money datatype

There will be many scenarios where your preferred option will still be - to use datatypes, such as decimal and float. But before skipping over this detail, take a look at your data and see if you can change your schema to the money datatype. After all, a 13% improvement in Analysis Services processing speed and 20% improvement in SSIS processing isn’t chump change.

Then, I found a few links with a lot of forums regarding money datatype

Cones of Money Datatype,

Hope this helps!!!


Similar Articles