# In Numbers Strength

## In Numbers Strength

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.

Table 3-3: Exact Numeric Data Types

SQL99

Oracle 9i

DB2 UDB 8.1

MS SQLSERVER 2000

INT[EGER]

NUMBER(38)

INT[EGER]

INT[EGER]

BIGINT

BIGINT

SMALLINT

SMALLINT

SMALLINT

SMALLINT

NUMBER(38)

TINYINT

NUMERIC[(p[,s])] OR DEC[IMAL][(p[,s])]

NUMERIC[(p[,s])]

NUMERIC[(p[,s])]

NUMERIC[(p[,s])]

DEC[IMAL] [(p[,s])]

DEC[IMAL]
[(p[,s])]

DEC[IMAL]
[(p[,s])]

NUMBER[(p[,s])

MONEY

SMALLMONEY

### 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 * 10125 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 231 (2,147,483,648) to positive 231 – 1 (2,147,483,647).

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

• BIGINT is an eight-byte integer with precision of 19 digits. It ranges from negative 263 –1 (9,223,372,036,854,775,808) to positive 263 (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 231 + 1 to positive 231 – 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 231 to positive 231 – 1. It occupies four bytes.

• BIGINT is to store large integers from negative 263 through positive 263 – 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 215 to positive 215 – 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 1038 +1 through positive 1038 – 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'
```
 Note MS SQL Server has literal formats for MONEY and SMALLMONEY data types represented as strings of numbers with an optional decimal point optionally prefixed with a currency symbol: ```\$12 \$542023.14```

### 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.

Table 3-4: Approximate Numeric Data Types

SQL99

Oracle 9i

DB2 UDB 8.1

MS SQL SERVER 2000

FLOAT[(p)]

FLOAT[(p)] NUMBER

FLOAT[(p)]

FLOAT[(p)]

REAL

REAL NUMBER

REAL

REAL

DOUBLE PRECISION

DOUBLE PRECISION NUMBER

DOUBLE [PRECISION]

DOUBLE PRECISION

 Note A classic example is number p, which is usually approximated to 3.14. The number was known in ancient Babylon and Egypt some 4,500 years ago and has been a matter of interest for mathematicians from Archimedes to modern scientists. As of today, 206,158,430,208 (3 * 236) decimal digits of p have been calculated. It would take approximately forty million pages, or fifty thousand volumes to store it in written form!

### 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.

 Note In addition to positive precision, Oracle allows negative precision as well. For example, if you have a column specified as NUMBER(10, –2), all inserted values will be implicitly rounded to the second significant digit. For example, 6,345,454,454.673 will be stored as 6,345,454,500

### 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.  BackCover  SQL Bible  Preface  Part I: SQL Basic Concepts and Principles  Chapter 1: SQL and Relational Database Management Systems (RDBMS)  Chapter 2: Fundamental SQL Concepts and Principles  Chapter 3: SQL Data Types  No Strings Attached  In Numbers Strength  Once Upon a Time: Date and Time Data Types  Object and User-Defined Data Types  Other Data Types  NULL  Summary  Part II: Creating and Modifying Database Objects  Part III: Data Manipulation and Transaction Control  Part IV: Retrieving and Transforming Data  Part V: Implementing Security Using System Catalogs  Part VI: Beyond SQL--Procedural Programming and Database Access Mechanisms  Part VII: Appendix  List of Figures  List of Tables  List of Code Examples  List of Sidebars  CD Content