Float vs. Decimal data types in Sql Server

 

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

Comments

Unknown said…
You've got some fuzzy math going on here. Decimals do not lose precision.

If 1800 = 1 * (54 / .03)
Then 1 = 1800 / (54 / .03)

Your formula shows:
1 = 1800 / 54 * .03

Huge flaw in your arithmetic.
Unknown said…
Nice post very helpful

dbakings
Daisy Thomas said…
An impressive share! I have Malaysia free game live22 just forwarded this onto a coworker who has been doing a little research on this. And he in fact bought me dinner because I discovered it for him... lol. So let me reword this.... Thank YOU for the meal!! But yeah, thanks for spending time to talk about this topic here on your website. The Gaming Club bears a license from the government of Gibraltar, and claims to be one of a prefer few casinos that have a license from the Gibraltar government. A devotee of the Interactive Gaming Council (IGC), The Gaming Club follows all the guidelines laid beside by the organization, something that has in imitation of a long quirk in it swine attributed as a good place to gamble online.

Everything about The Gaming Club feels good; be it the promotions, the big number of games, the multipart banking options upon offer, the unprejudiced security measures, or the fair and held responsible gaming practices the casino adopts.

The Gaming Club motors along on software developed by one of the giants of online gaming software press forward Microgaming. The software it uses is broadminded and has a range of features designed to complement your online gambling experience and make you desire to arrive urge on after every circular of gambling you do here.

Another hallmark of a good casino is the quality of its customer retain team, and The Gaming Club does not disappoint upon this front.
http://magnum4dlive.com/

Popular posts from this blog

RAM Disks do not speed up Visual Studio

SpreadsheetGear vs. SyncFusion vs. ComponentOne

Outlook tip: Turn off Email Contact Pictures