Database management systems (DBMS) have well-defined limits for each type of data. You need to know the minimum and maximum values to correctly establish the type of each column. A column that has the right type for the data it contains will perform well and avoid bugs where the data exceeds the set limit. This article discusses the limitations of integer types in SQL.
Numeric types for MySQL, PostgreSQL, SQL Server…
Data of numeric types can usually be stored in a column of type tinyint, smallint, int, or bigint. However, it is necessary to rely on the documentation of each system to know precisely the existing types. The table below shows the existing types in each of the largest existing systems:
|Data types||MySQL||PostgreSQL||SQL Server||DB2||Firebird|
|SMALLINT / INT2||✔||✔||✔||✔||✔|
|INT / INTEGER / INT4||✔||✔||✔||✔||✔|
|BIGINT / INT8||✔||✔||✔||✔||✔|
To know : Oracle stores numeric type data only as a single type called NUMERIC whose maximum value is 10125.
Limit values of digital data
Each of the data types presented earlier is limited. For almost any system the numeric data can be negative (signed column), but for MySQL it is possible to store only positive numeric values (unsigned column) which increases the maximum limit.
|Types||Size (in bytes)||Min value||Max value|
|SMALLINT / INT2
SMALLINT / INT2 (unsigned)
|INT / INTEGER / INT4
INT / INTEGER / INT4 (unsigned)
|BIGINT / INT8
BIGINT / INT8 (unsigned)
Note (SQL Server): the tinyint of SQL Server is unsigned, so the values range from 0 to 255.
Note (PostgreSQL): the types INT2, INT4 and INT8 are specific to PostgreSQL. Do not try to find them on another database management system.
Choose the right type
Depending on the data to be stored, it is important to choose the type of a column. The stored data will be the same but the space on the disk will be different. To make an analogy, it’s like storing a matchbox either in a drawer or in a warehouse. A whole warehouse to store only a box of matches, that’s a bit too much. However, if several thousand matchboxes have to be stored, a single drawer will not suffice and the warehouse is more suitable.
Choosing the right type of data improves performance a bit. But you have to be careful, because an application that underestimates the data to be stored will have a bug if it needs to record more information than expected.
The first real-life example is that a blog can have a bug if comments are stored are stored on a table that use an ID of type smallint and there are more than 32,767 comments in the table.
Let us take an example to illustrate this point. Imagine a blog that uses MySQL and that has a table that contains comments posted by Internet users. A field in this table stores IDs which increment with each new comment. There is no point in choosing a signed type since the ID will always be greater than zero.
Also, imagine that the ID is stored on an unsigned smallint (0 to 65.536). If the blog is very successful, there will be a bug the day the limit of 65,536 comments is reached. For a small hobbyist blog that’s more than enough, but for a site with huge ambition and potentially getting spammed a lot, this figure can be reached faster than expected.