Data Types in SQL
Table of contents
Since a database is a collection of information, it can store names, numbers, images, calculations, financial amounts, characters and so on. This stored information is what you call data, which you can change or manipulate anytime you want. When you start providing rules on how you write and store data, then you are dealing with data types. Data types take into consideration the length allocated by the database for every column in the table and what values it could contain - whether it is alphanumeric, just numbers, graphics, date or time. By defining what data is stored in each database field, you are preventing the occurrence of data entry errors. This form of validation that controls incorrect data to be entered into the database is also called field definition.
Data Types
INTEGER – This consists only of whole numbers that are both positive and negative. It does not contain a decimal nor a fractional part. The value ranges from -2,147,483,648 to 2,147,483,647, with an allocated 4 bytes of storage size.
SMALLINT – This is used in replacement of integers to save storage space, but with a precision that cannot be larger than that of an integer. Precision in computer programming is the maximum total of significant digits a number can have. The value ranges from -32,768 to +32,767, with an allocated 2 bytes of storage size.
BIGINT – This is the reverse of the SMALLINT, where its minimum precision is the same as the INTEGER data type or greater. The value ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, with an allocated 8 bytes of storage size.
NUMERIC (p, s) – In addition to the integer part, this data type also contains a fractional component that indicates the precision and scale of the value. Scale is the number of digits or places reserved in a fractional part of the data, located at the right side of the decimal point. In NUMERIC (p, s), ‘p’specifies the precision while ‘s’ is for the scale. For example, NUMERIC (6, 3) means that the number’s absolute value will only be up to 999.999 (6 total significant digits with 3 digits following the decimal point).
DECIMAL (p, s) – Like the NUMERIC data type, this has a fractional component where you can specify both the value precision and scale. However, this data type
allows greater precision. For example, DECIMAL (6, 3) can contain values up to 999.999 but the database will still accept values larger than 999.999. Let us say you entered the number 123.4564, this will be rounded off to 123.456. The allocated storage size for this data type is based on the given precision.
REAL (s) – This consists of a single-precision, floating- point number where the decimal point can “float” to different places in the said number. This means that this data type’s decimal value has a limitless precision and a scale of variable lengths. For example, the values for π (pi) can include 3.14159, 3.14 and 3.1 (each value has its own precision). For single-precision, floating point numbers, their precision is between 1 and 21 inclusive. It also allocates 4 bytes of storage size for its values.
CHARACTER (n) or CHAR (n) – This data type is also known as a fixed-length string or a constant character. This means that all the strings stored in that particular column have the same length, which is represented by ‘n’ (the number of characters or the maximum allocated length for the defined field). For example, if you set the column’s data type to CHAR(23) then the maximum length of any data entered in the field is 23 characters. If the string’s length is less than 23, then SQL fills the remaining spaces with blanks. This is the drawback of using fixed-length strings because storage space is wasted. On the other hand, if there is no value provided for ‘n’, then SQL assumes a length of one character. The maximum length for the CHARACTER data type is 254.
CHARACTER VARYING (n) or VARCHAR (n) – This is used when the data entries are of different lengths, or not constant, but you don’t want SQL to fill the remaining spaces with blanks. Thus, the exact number of characters you enter will
be stored in the database - further saving storage space. This data type has no default value and its maximum length is 32,672 characters.
CHARACTER LARGE OBJECT (CLOB) – data type is used to contain unicode character-based information that is too big to be stored as a CHARACTER type, such as large documents. The maximum value of a CLOB is up to 2,147,483,647 characters long.
DATE – This data type provides storage for the year, month and day values of a date, in that particular order. The year value is expressed using four digits, which can be represented by any value ranging from 0001 up to 9999. As for the month and day values, they are both expressed using two digits. The format for the date data type is yyyy-mm-dd.
TIME – This data type stores and displays time values with an hour-minute-second format (“HH:MM:SS”).
DATETIME – When the value contains both date and time information then you use the DATETIME data type, which is displayed using the “YYYY-MM-DD HH:MM:SS” format. The valid range of values for this type is from “1000-01-01 00:00:00” to “9999-12-31 23:59:59”.
TIMESTAMP – This is similar to the DATETIME data type but the range of values is from “1970-01-01 00:00:01” UTC to “2038-01-19 03:14:07” UTC.
Boolean – This data type consists of values that are used for data comparison: TRUE, FALSE, or NULL. For data to be returned, all the conditions of the specified criteria for a given query should be met, meaning the Boolean value is TRUE. If data is not returned, then the value is either FALSE or NULL.