Home > SQL > BISQL#8:Pros and Cons of Money Data Type

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:

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

Hope this helps!!!

This post is on the basis of my BI Blog post

For Money and SQL Money Data Type Be Care full while using !!!! « (B)usiness (I)ntelligence Mentalist

 

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

                | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                   Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: SQL
  1. September 2, 2011 at 9:46 pm

    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 !!!

  1. June 30, 2011 at 10:15 pm
  2. June 30, 2011 at 10:20 pm

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

%d bloggers like this: