VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

Approximate Numeric Types

Use approximate numeric data types to represent approximate numerical values. An

approximate numerical value has these characteristics:

• It can be a negative, zero, or positive number.

• It's considered to be an approximation of a floating-point (real) number.

• It typically is used to represent the very small or very large quantities common in technical, scientific, statistical, and financial calculations.

• It's expressed in scientific notation. A number in

  scientific notation is written as a decimal number multiplied by an (integer) power of 10. An uppercase

  E is the exponentiation symbol: 2.5E2 = 2.5 x 10² = 250, for example. The

  mantissa is the portion that expresses the significant digits (2.5 here), and the

  exponent is the power of 10 (2 here). The mantissa and exponent each can have a sign: 2.5E2 = 2.5 x 10² = 0.025.

• It has a fixed precision but no explicit scale. (The sign and magnitude of the exponent determine the scale intrinsically.) The precision is the number of (binary) bits used to store the mantissa. To convert from binary to decimal precision, multiply the precision by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. For example, 24 bits yields 7 digits of precision, and 53 bits yields 15 digits of precision.

• It's one of the types listed in

  Table 3.10 .

  Table 3.10. Approximate Numeric Types

  T YPE	D ESCRIPTION
  FLOAT	Represents a floating-point number, stored in a column defined as FLOAT( precision ). precision is greater than or equal to 1 and expressed as the number of bits (not the number of digits); the maximum precision depends on the DBMS.
  REAL	This data type is the same as FLOAT except that the DBMS defines the precision. REAL numbers usually are called single-precision numbers. REAL takes no arguments.
  DOUBLE PRECISION	This data type is the same as FLOAT except that the DBMS defines the precision, which must be greater than that of REAL. DOUBLE PRECISION takes no arguments.

Tips

• Don't enclose a numeric literal in quotes.

• 

  Table 3.11 lists approximate numeric and similar types for the DBMSes. See the DBMS documentation for size limits and usage restrictions. DBMSes often accept type names that they don't implement, converting them to suitable, supported types;

  PostgreSQL converts float to double precision, for example.

  Table 3.11. DBMS Approximate Numeric Types

  DBMS	T YPES	DBMS	T YPES
  Access	single, double	DB2	real, double
  SQL Server	float, real	MySQL	float, double
  Oracle	binary_float, binary_double	PostgreSQL	double precision, real