Sql Server 2005 Data Types

What is the diff between real, float, decimal and money. And most important, when would I use them. Like I understand - real and float are approx. types, meaning they dont store the ex开发者_如何转开发act value. Why would you ever want this?

Thanks

real and float numeric types are useful to handle a very wide range of values as is encountered with physical dimensions or mathematical results.

The loss of precision they incur, for example when adding values which are not in the same range, for example 0.00002468 + 1.23E9 (i.e. 1,230,000) is typically acceptable for practical uses. This is a small tribute to pay to the relatively compact storage requirements of these floating point types.

The decimal and money types do not cover such a broad range (yet they cover ranges that are beyond most typical accounting applications), and do not exhibit any of this lossy behavior with rounding and such.

See MS-SQL document for detailed information. The following table provides an indicative precision, range and storage requirement for various types.

Type         Max value               precision(*)   Storage
money        +/-922,000,000,000,000     3 (4?)      8 bytes
smallmoney   +/-200,000                 3?          4 bytes
decimal      varies (as defined)       varies       varies 3 to 17

real         +/- 3.4 * 10^38            7 digits  4 bytes
float "56"   +/- 1.7 * 10 ^308         15 digits  8 bytes   (float can also be declared to be just like a real)       

(*) precision : For the "exact" types, this is the number of digits after the decimal point. For the "lossy" reals and floats, this is the number of significant digits.

Money is an exact data type. as in it is continuous between its upper and lower bound. You would generally use it when you want to store values of money and don't want to lose precision and get rounding errors caused by IEEE754. Decimal is a similarly an exact data type that isn't lossy up to a certain number of decimal places (which you can specify). Real is equivalent to float(24).

To be clear, precision loss can still occur when using division, but all other basic mathematical operations do not incur precision loss for Money and decimal types.

See here for an explanation of the various Transact SQL data types.