PostgreSQL SMALLINT vs INT
Introduction
There are several integer types supported by SQL in general such as INT, BIGINT, SMALLINT and TINYINT. But in this article we discuss SMALLINT vs INT and see how they are different from each other and which one is more appropriate depending on the situation. So let’s begin!
SMALLINT vs INT
SMALLINT and INT are both integer numeric data types that are used to store data which is essentially of integer type. Now, Let us compare both of them in terms of storage size, minimum value, maximum value and then we will follow with a few examples.
Storage Size SMALLINT VS INT
In terms of storage size, the storage size of SMALLINT is 2 bytes while the storage size of INT is double of that i.e.: 4 bytes.
Minimum Value SMALLINT VS INT
The minimum value for SMALLINT is -32,768 (-2^15) while the minimum value for INT is -2,147,483,648 (-2^31).
Maximum Value SMALLINT VS INT
The maximum value for SMALLINT is 32,767 (2^15-1) while the maximum value for INT is 2,147,483,647 (2^31-1).
Examples
SMALLINT Example
1 2 3 | DECLARE @value SMALLINT SET @value = 800 PRINT @value |
OUTPUT:
1 | 800 |
INT Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 800 PRINT @VALUE |
OUTPUT:
1 | 800 |
SMALLINT Storage Size Example
1 2 3 | DECLARE @VALUE SMALLINT SET @VALUE = 800 PRINT DATALENGTH (@VALUE) |
OUTPUT:
1 | 2 |
INT Storage Size Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 800 PRINT DATALENGTH (@VALUE) |
OUTPUT:
1 | 4 |
SMALLINT Huge Value Storage Example
1 2 3 | DECLARE @VALUE SMALLINT SET @VALUE = 96548 PRINT @VALUE |
OUTPUT:
1 | OVER Flow ERROR! |
INT Huge Value Storage Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 9458796 PRINT @VALUE |
OUTPUT:
1 | OVER Flow ERROR! |
Choosing the Correct Data Type
In databases, it is very crucial that you choose the right data type. If you choose a bigger data type for smaller inputs in the table or you choose small data type for data which is enormous in size then in both cases you will have performance issues. Large data type with small data would work but it would cause performance problems as the data grows in future. If we talk about the disk space then it can be reduced more using SMALLINT in comparison to the INT. Let us assume that we have a column named as some_id in database then you have to choose SMALLINT for such type because choosing INT would cause the performance issues and would make the database inefficient. Make sure you choose the correct data type while keeping the scalability factor in mind.
Conclusion
As we have talked discussed it really depends upon your specific needs in selecting the right data type. In most cases, it has been observed that the use of SMALLINT reduces the disk space greatly in comparison to INT and hence it increases performance. Please analyze your specific use case and choose the data type which fits your purpose. Good luck!
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started