Numeric data types are numbers stored in database columns.
Exact numeric types, values where the precision and scale need to be preserved. The exact numeric types are INTEGER, BIGINT, DECIMAL, NUMERIC, NUMBER, and MONEY.
Approximate numeric types, values where the precision needs to be preserved and the scale can be floating. The approximate numeric types are DOUBLE PRECISION, FLOAT, and REAL.
decimal and numerics
Numeric data types that have fixed precision and scale.
decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
s (scale)
The number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. The scale must be a value from 0 through p. The scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
float and real
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
| Data type | Range | Storage |
|---|---|---|
| float | - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 | Depends on the value of n |
| real | - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 | 4 Bytes |
Syntax
float [ (n) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.
int, bigint, smallint and tinyint
Exact-number data types that use integer data.
| Data type | Range | Storage |
|---|---|---|
| bigint | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | 8 Bytes |
| int | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) | 4 Bytes |
| smallint | -2^15 (-32,768) to 2^15-1 (32,767) | 2 Bytes |
| tinyint | 0 to 255 | 1 Byte |
money and smallmoney
Data types that represent monetary or currency values.
| Data type | Range | Storage |
|---|---|---|
| money | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
| smallmoney | - 214,748.3648 to 214,748.3647 | 4 bytes |