Using decimal, float, and real Data

The decimal data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

The two attributes that define decimal columns, variables, and parameters are:

• p
  Specifies the precision, or the number of digits the object can hold.
• s
  Specifies the scale or number of digits that can be placed to the right of the decimal point.
  p and s must observe the rule: 0 <= s <= p <= 38.

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified.

For more information about how mathematical operations affect the precision and scale of the result, see Precision, Scale, and Length (Transact-SQL).

Using float and real Data

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server 2005 uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

See Also

Other Resources

Data Types (Transact-SQL)
decimal and numeric (Transact-SQL)
float and real (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History