BISQL#8:Pros and Cons of Money Data Type
Hi Friends,
When I started goggling for different data type problem got lot of interesting things came out,In which money is one of the best and we should be always thinking before we use money data type.
Following is script and output for money data type:
So we can see money with float operation is not shows required output.
Now you will say I want comparison between decimal and money
I found Comparison on following link and copied few data as well 
:
The Many Benefits of Money…Data Type! – Technical Notes
Money vs. Decimal vs. Float Decision Flowchart
Below is a high-level decision flowchart to help you decide which data type 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.
In that link I found lots of good feature about money data type
Money (Data Type) Internals
The reason for the performance improvement is because of 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; the 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 data type needs additional parsing and CPU cycles to handle than a fixed-width type.
Let’s compare the different data types that are typically used with money (data types).
The key here is that the money data type 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 feature of money Data type
There will be many scenarios where you preferred option will still be to use data types 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 data type. After all, a 13% improvement in Analysis Services processing speed and 20% improvement in SSIS processing isn’t chump change.
Then I found few links where lot of forums regarding money data type.
Cones in Money Data type
SQL Server Forums – CSV import problem with Money data-type
SQL Server Forums – problem with money data type
Performing Division Operation and inserting into money datatype Expression
Stupid problem with money datatype
Hope this helps!!!
This post is on the basis of my BI Blog post
Success is the sum of small efforts, repeated day in and day out…
……………….. Robert J. Collier
For more interesting information on SQL we can also look into similar topics such as
· BISQL # 9 :How to Get Answers,Common Question :SQL Server FAQs eBook
· BISQL # 10 :Top 10 Things For SQL Server Performance Part – I
· BISQL # 12 :SQL Server Technical White Papers,All in one
· BISQL # 13 :Concept of Change Data Capture (CDC) in SQL Server 2008 Part –I
· BISQL # 14 :Concept of Change Data Capture (CDC) in SQL Server 2008 Part –II
Hope you will like this post on Pros & Cons of Money data type.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 
Hi all this vishal again if you havent started using money Data type So please not to us it and have experinment its always good to have decimal datatype rather than money as am seeing visit to this page is more i am just think of giving this additional thought..
One thing also you can do just convert money to decimal if u still finds problem !!!