For Money and SQL Money Data Type Be Care full while using !!!!
My current BI project is all about calculation ..
Lot of money involvement as well ..
Also a lot of complex database calculation is required ..
So based on that when we are developing any table and its data type we have to always be sure it will not create any problem in future as well for web application and database application
When I started googling for above 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:
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 :
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
So after reading all posts we have decided to change data type money to decimal.
Success is the sum of small efforts, repeated day in and day out…. Robert J. Collier