Float vs. Decimal data types in Sql Server

http://josephfluckiger.blogspot.in/2009/06/float-vs-decimal-data-types-in-sql_25.html

 

This is an excellent article describing when to use float and decimal. Float stores an approximate value and decimal stores an exact value.

In summary, exact values like money should use decimal, and approximate values like scientific measurements should use float.

Here is an interesting example that shows that both float and decimal are capable of losing precision. When adding a number that is not an integer and then subtracting that same number float results in losing precision while decimal does not:

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;
SET @Float1 = 54;
SET @Float2 = 3.1;
SET @Float3 = 0 + @Float1 + @Float2;
SELECT @Float3 – @Float1 – @Float2 AS “Should be 0”;

Should be 0
———————-
1.13797860024079E-15

 

When multiplying a non integer and dividing by that same number, decimals lose precision while floats do not.

DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4);
SET @Fixed1 = 54;
SET @Fixed2 = 0.03;
SET @Fixed3 = 1 * @Fixed1 / @Fixed2;
SELECT @Fixed3 / @Fixed1 * @Fixed2 AS “Should be 1”;

Should be 1
—————————————
0.99999999999999900

AT 7:51 AM

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

%d bloggers like this: