SQL Bible [Electronic resources] نسخه متنی

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.

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

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.

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'

$12
$542023.14

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.