Microsoft SQL Server 2008 Bible

All numeric data could generally be divided into two categories: exact numbers and approximate numbers.

Exact numbers

Exact numbers can either be whole integers (numeric primary keys, quantities, such as number of items ordered, age) or have decimal points (prices, weights, percentages). Numbers can be positive and negative and have precision and scale. Precision determines the maximum total number of decimal digits that can be stored (both to the left and to the right of the decimal point). Scale specifies the maximum number of decimals allowed. Exact numeric data types are summarized in Table 3-3.

SQL99

SQL99 specifies the following data types for exact numbers: INTEGER, SMALLINT, NUMERIC, DECIMAL (as well as some synonyms found in Table 3-3).

• INTEGER represents countable numbers; its precision is implementation-specific.

• SMALLINT is virtually same as INTEGER, but maximum precision can be smaller than that for INTEGER.

• NUMERIC data type supports storage of numbers with specific decimal component as well as whole numbers. Optional scale specifies the number of decimal locations supported.

• DECIMAL is very similar to NUMERIC. The only difference is the precision (but not the scale) used by a vendor-specific implementation can be greater than that used in declaration.

Oracle 9i

Oracle has one data type, NUMBER, to represent all numeric data and numerous synonyms for it to comply with SQL99 (see Table 3-3). INTEGER and SMALLINT will translate into NUMBER(38); NUMERIC and DECIMAL will be substituted with NUMBER. The NUMBER data type stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 * 10^–130 and 9.9...9 * 10¹²⁵ with 38 digits of precision. The space is allocated dynamically, so Oracle claims having one numeric data type for all numeric data won't hurt performance.

DB2 UDB 8.1

DB2 has four data types for exact numbers: INTEGER, SMALLINT, BIGINT, and DOUBLE.

• INTEGER is a four-byte integer with a precision of 10 digits. It can store values from negative 2³¹ (2,147,483,648) to positive 2³¹ – 1 (2,147,483,647).

  Precision and Scale for NUMERIC and DECIMAL Datatypes

  NUMERIC and DECIMAL values' scale and precision often cause confusion. Please remember, precision specifies the maximum number of ALL digits allowed for a value. For example, if a hypothetic table has these columns

  column1 NUMERIC(10, 4) column2 NUMERIC(10,2) column3 NUMERIC(10,0)

  then the maximum number you can store in column1 is 999,999.9999; column2 can hold values up to 99,999,999.99 inclusive; and column3 is good enough for 9,999,999,999. To determine the maximum number of figures before the decimal point, subtract scale from precision. If you try to insert a value with more figures before the decimal point than column allows, you will get an error, but values with more decimal points than specified will simply be rounded. For example, 999,999.9999 inserted into column2 (or column3) will be rounded to 1,000,000, but an attempt to set column1 to 99,999,999.99 would fail.

• SMALLINT is reserved for smaller size integers. The storage size is two bytes, and the range is from negative 2¹⁵ (32,768) to positive 2¹⁵ – 1 (32,767).

• BIGINT is an eight-byte integer with precision of 19 digits. It ranges from negative 2⁶³ –1 (9,223,372,036,854,775,808) to positive 2⁶³ (9,223,372,036,854,775,807).

• DECIMAL data type (corresponds to NUMERIC) is designated for decimal numbers with an implicit decimal point. The maximum precision is 31 digits, and the range is from negative 2³¹ + 1 to positive 2³¹ – 1.

MS SQL Server 2000

MS SQL Server has more numeric data types for exact numeric data than Oracle and DB2. In addition to INT, BIGINT, SMALLINT, and TINYINT it also offers MONEY and SMALLMONEY.

• INT (or INTEGER) is to store whole numbers from negative 2³¹ to positive 2³¹ – 1. It occupies four bytes.

• BIGINT is to store large integers from negative 2⁶³ through positive 2⁶³ – 1. The storage size is eight bytes. BIGINT is intended for special cases where INTEGER range is"not sufficient.

• SMALLINT is for smaller integers ranging from negative 2¹⁵ to positive 2¹⁵ – 1

• TINYINT is convenient for small nonnegative integers from 0 through 255. It only takes one byte to store such number.

• DECIMAL is compliant with SQL99 standards. NUMERIC is a synonym to DECIMAL. (See Table 3.3 for other synonyms.) Valid values are in the range from negative 10³⁸ +1 through positive 10³⁸ – 1.

• MONEY is a special eight-byte MS SQL Server data type to represent monetary and currency values. The range is from negative 922,337,203,685,477.5808 to positive 922,337,203,685,477.5807 with accuracy to a ten-thousandth.

• SMALLMONEY is another monetary data type designated for smaller amounts. It is four bytes long and can store values from negative 214,748.3648 to positive 214,748.3647 with the same accuracy as MONEY.

  Note

  Why have special data types for monetary values? One good reason is consistency. Probably all accountants know how much trouble so-called rounding errors can cause. For example, one column for dollar amounts is declared as NUMERIC(12,2) and another is NUMERIC(14,4). If we operate large sums, discrepancies can easily reach hundreds and even thousands of dollars. From another point of view, many different data types for virtually the same entities can cause confusion, so Oracle has its reasons for allowing only one data type for all numeric data. We'll let you decide which approach has more validity.

Literals for exact numbers

Literals for exact numbers are represented by string of numbers optionally preceded by plus or minus signs with an optional decimal part for NUMERIC and DECIMAL data types separated by a dot (.):

123 -33.45 +334.488

Oracle optionally allows enclosing literals in single quotes:

'123' '-677.34'

The incorrect use of data types is quite typical for inexperienced database developers and can result in serious problems.

For example, defining a money-related field as a FLOAT or NUMERIC(12,1) causes rounding errors. (Accountants are just going to hate you!) Insufficient precision for a primary key column (say, ORDHDR_ID_N NUMBER(5) in an ORDER_HEADER table) will work for a while, but after inserting a certain number of records (99,999 in our case), you will not be able to insert new rows anymore — the next value for the primary key (100,000) won't fit NUMBER(5) precision.

The last example is easily fixable — the precision of a numeric column can easily be adjusted (more details in the next chapter). That is one of the benefits of a relational database over the old legacy systems. But still, it might take some time to figure out what causes the problem and fix it, and if your database is, for example, a large 24/7 order management system, your users are not going to be happy with the delay.

Approximate numbers

Approximate numbers are numbers that cannot be represented with absolute precision (or don't have a precise value). Approximate numeric data types are summarized in Table 3-4.

SQL99

SQL99 specifies the following data types for approximate numbers: FLOAT, REAL, and DOUBLE PRECISION.

• FLOAT is to store floating-point numbers with precision optionally specified by user.

• REAL is similar to FLOAT, but its precision is fixed.

• DOUBLE PRECISION is virtually the same as REAL, but with a greater precision.

Oracle 9i

As we already know, Oracle has one numeric data type, NUMBER, for both exact and approximate numbers. Another supported data type is FLOAT, which is mostly used to represent binary precision. The maximum decimal precision for FLOAT is 38; maximum binary precision is 126.

DB2 UDB 8.1

DB2 has REAL single-precision data type as well as DOUBLE double-precision data type for approximate numbers. FLOAT is a synonym to DOUBLE.

• REAL is a four-byte long approximation of a real number. The range is from negative 3.402E + 38 to negative 1.175E – 37 or from positive 1.175E – 37 to 3.402E + 38. It also includes 0.

• DOUBLE requires eight bytes of storage and is much more precise than REAL. The number can be zero or can range from –1.79769E + 308 to –2.225E – 307, or from 2.225E - 307 to 1.79769E + 308.

MS SQL Server 2000

MS SQL Server has one data type for floating-point numbers — FLOAT. It also has a number of synonyms for SQL99 compliance (Table 3-4).

FLOAT data type can hold the same range of real numbers as DOUBLE in DB2. The actual storage size can be either four or eight bytes.

Literals for approximate numbers

In addition to literals for exact numbers you can specify a real number as two numbers separated by upper- or lowercase character E (scientific notation). Both numbers may include plus or minus; the first number may also include a decimal point:

+1.23E2 -3.345e1 -3.44488E+002

The value of the constant is the product of the first number and the power of 10 specified by the second number.