Whole numbers can be stored using INTEGER data type. The range of values that can be stored using this data type is from -2,147,483,647 to +2,147,483,647. The negative number -2,147,483,648 cannot be used because it is a reserved value. All the values declared as INTEGER are stored as binary integers; they need 4 bytes of memory to be stored. They always have a zero scale. This data type is generally used to store counts, quantities and any other values that can be stored using whole numbers.
This data type can be used in arithmetic operations, because they do not usually cause rounding errors. These operations are performed more efficiently with the data of INTEGER data type if compared to the DECIMAL and FLOAT data types. However, the fractional part of a number assigned to the INTEGER data type is always discarded.
INTEGER data type is used to store SERIAL values used in a database. 4GL automatically assigns the next whole number to the column defined as SERIAL when a row is inserted into a table. The SERIAL number cannot be changed once assigned and it cannot be entered by a user.
BIGINT is used to store 8-byte whole numbers whereas INTEGER data type can store only 4-byte numbers. The BIGINT data type can store values in a range from -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807.
Thus the BIGINT data type is typically used to store values which exceed the limits of INTEGER values. It can also be used to store values from the database columns of INT8 or SERIAL8 data types. The BIGINT data type can pass values to C functions, where they can be converted into a 8-byte C whole-number data type.
SMALLINT data type is a data type similar to INTEGER but with the narrower range of allowed values. It is also stored as a binary integer but requires only 2 bytes for storage due to its small range of values. The value that can be assigned to the SMALLINT data type must be whole numbers that range from -32,767 to +32,767. All the decimal places, if any, are discarded. The negative value -32,768 cannot be used in a database column of a SMALLINT type and cannot be entered to a form field defined as SMALLINT, because this value is reserved.
A value whose actual value is less than 215 can be stored in a variable of SMALLINT data type. This data type is generally used to store, display and manipulate any values that can be represented as whole numbers, these are:
Due to the fact that only 2 bytes are required to store a SMALLINT value, highly efficient arithmetic operations can be performed with values of this type.
TINYINT is an integer data type used for storing small values. The range of values that can be stored using this data type is from -128 to 127. All the values declared as TINYINT are stored as binary integers; they need 1 byte of memory to be stored. They always have a zero scale.
This data type can be used in arithmetic operations, because they do not usually cause rounding errors. This data type is used for performance efficiency, if you need to store small numbers.
The TINYINT variables are initialized to 0 when they are declared. They can never be initialized to NULL.