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

DECLARE @VALUE INT
SET @VALUE = 700
PRINT @VALUE

OUTPUT:

700

BIGINT Example

DECLARE @VALUE BIGINT
SET @VALUE = 700
PRINT @VALUE

OUTPUT:

700

INT Storage Size Example

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

OUTPUT:

4

BIGINT Storage Size Example

DECLARE @VALUE BIGINT
SET @VALUE = 700
PRINT DATALENGTH (@VALUE)

OUTPUT:

8

INT huge value storage Example

DECLARE @VALUE INT
SET @VALUE = 9458796548
PRINT @VALUE

OUTPUT:

OVER Flow ERROR!

BIGINT huge value storage Example

DECLARE @VALUE BIGINT
SET @VALUE = 9458796548
PRINT @VALUE

OUTPUT:

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

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.