SQL Server Decimal/Money/Float datatypes

When dealing with money, or numbers, sql server provides a few different datatypes: Decimal, Money, Float among others.

The main difference is that Decimal and Money datatypes are “exact”, as opposed to Float which is a “Floating Point Representation”.

When dealing with Floats, you can get some odd mathematical results:
http://www.chrismay.org/2005/09/29/BinaryRepresentationOfDecimalValues.aspx

e.g.
0.4 – 0.1 = 0.30000000000000000000004
1.4-1.1 = 0.29999999999999999999998

So when a number is going to represent money, you should use the Decimal or Money datatypes.  Money is (19,4) which means 19 total digits, 4 of which are right of the decimal point.  Decimal can be setup however you want, like Decimal(10,2).

SmallMoney can also be used, which can represent money values under 1,000,000 w/ 4 decimal places.

 

Advertisements

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