INT vs BIGINT
Introduction
There are several integer types supported by SQL in general such as INT, BIGINT, SMALLINT and TINYINT. But today we’ll discuss INT vs BIGINT and see how they are different from each other. So let’s begin!
INT and BIGINT are both integer numeric data types. Let us compare both of them in terms of storage size, minimum value, maximum value and then we will follow with a few of the examples.
Storage Size (INT VS BIGINT)
If we talk about the storage size of INT and BI GINT, the storage size of INT is 4 bytes while the storage size of BIGINT is double of that i.e.: 8 bytes.
Minimum Value (INT VS BIGINT)
The minimum value for INT is -2,147,483,648 (-2^31) while the minimum value for BIGINT is -9,223,372,036,854,775,808 (-2^63).
Maximum Value (INT VS BIGINT)
The maximum value for INT is 2,147,483,647 (2^31-1) while the maximum value for BIGINT is 9,223,372,036,854,775,807 (2^63-1).
INT Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 700 PRINT @VALUE |
OUTPUT:
1 | 700 |
BIGINT Example
1 2 3 | DECLARE @VALUE BIGINT SET @VALUE = 700 PRINT @VALUE |
OUTPUT:
1 | 700 |
INT Storage Size Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 700 PRINT DATALENGTH (@VALUE) |
OUTPUT:
1 | 4 |
BIGINT Storage Size Example
1 2 3 | DECLARE @VALUE BIGINT SET @VALUE = 700 PRINT DATALENGTH (@VALUE) |
OUTPUT:
1 | 8 |
INT huge value storage Example
1 2 3 | DECLARE @VALUE INT SET @VALUE = 9458796548 PRINT @VALUE |
OUTPUT:
1 | OVER Flow ERROR! |
BIGINT huge value storage Example
1 2 3 | DECLARE @VALUE BIGINT SET @VALUE = 9458796548 PRINT @VALUE |
OUTPUT:
1 | 9458796548 |
How important is it to choose 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. Disk space usage can be reduced greatly by the use of INT instead of BIGINT. But again, remember that you choose the data type according to your need. If your need is to save disk space, then so be it. Make sure you choose the correct data type while keeping the scalability factor in mind.
Conclusion
As we have talked about that it really depends upon your need which would help you select the right data type. In most cases, it has been observed that the use of INT reduces the disk space a lot and hence it increases the performance. Let’s say if INT reduces the disk space by 20% then you would have 20% increased performance and vice versa in case of BIGINT. Again, we are not going black and white and not stating one data type as an absolute perfect, analyze your scenarios 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