Difference between numeric, float and decimal in SQL Server

Not a complete answer, but a useful link:

"I frequently do calculations against decimal values. In some cases casting decimal values to float ASAP, prior to any calculations, yields better accuracy. "

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/20/for-better-precision-cast-decimals-before-calculations.aspx

The case for Decimal

What it the underlying need?

It arises from the fact that, ultimately, computers represent, internally, numbers in binary format. That leads, inevitably, to rounding errors.

Consider this:

0.1 (decimal, or "base 10") = .00011001100110011... (binary, or "base 2")

The above ellipsis [...] means 'infinite'. If you look at it carefully, there is an infinite repeating pattern (='0011')

So, at some point the computer has to round that value. This leads to accumulation errors deriving from the repeated use of numbers that are inexactly stored.

Say that you want to store financial amounts (which are numbers that may have a fractional part). First of all, you cannot use integers obviously (integers don't have a fractional part). From a purely mathematical point of view, the natural tendency would be to use a float. But, in a computer, floats have the part of a number that is located after a decimal point - the "mantissa" - limited. That leads to rounding errors.

To overcome this, computers offer specific datatypes that limit the binary rounding error in computers for decimal numbers. These are the data type that should absolutely be used to represent financial amounts. These data types typically go by the name of Decimal. That's the case in C#, for example. Or, DECIMAL in most databases.

Decimal has a fixed precision while float has variable precision.

EDIT (failed to read entire question): Float(53) (aka real) is a double-precision (64-bit) floating point number in SQL Server. Regular Float is a single-precision (32-bit) floating point number. Double is a good combination of precision and simplicty for a lot of calculations. You can create a very high precision number with decimal -- up to 136-bit -- but you also have to be careful that you define your precision and scale correctly so that it can contain all your intermediate calculations to the necessary number of digits.

Although the question didn't include the MONEY data type some people coming across this thread might be tempted to use the MONEY data type for financial calculations.

Be wary of the MONEY data type, it's of limited precision.

There is a lot of good information about it in the answers to this Stackoverflow question:

Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?