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

DECLARE @value SMALLINT
SET @value = 800
PRINT @value

OUTPUT:

800

INT Example

DECLARE @VALUE INT
SET @VALUE = 800
PRINT @VALUE

OUTPUT:

800

SMALLINT Storage Size Example

DECLARE @VALUE SMALLINT
SET @VALUE = 800
PRINT DATALENGTH (@VALUE)

OUTPUT:

2

INT Storage Size Example

DECLARE @VALUE INT
SET @VALUE = 800
PRINT DATALENGTH (@VALUE)

OUTPUT:

4

SMALLINT Huge Value Storage Example

DECLARE @VALUE SMALLINT
SET @VALUE = 96548
PRINT @VALUE

OUTPUT:

OVER Flow ERROR!

INT Huge Value Storage Example

DECLARE @VALUE INT
SET @VALUE = 9458796
PRINT @VALUE

OUTPUT:

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.