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 Smile..

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:

image

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 Smile with tongue out:

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.

image

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).

image

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

Problem with money Datatype

MS SQL ::Money Problem

SQL Server Central

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: