Numeric data types

Numeric data types are numbers stored in database columns.

Exact numeric types are values where the precision and scale need to be preserved:

  • bigint
  • bit
  • decimal
  • int
  • money
  • numeric
  • smallint
  • smallmoney
  • tinyint

Approximate numeric types are values where the precision needs to be preserved and the scale can be floating:

  • double precision
  • float
  • 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