Difference between INT and BIGINT data type in Sql Server

Both INT and BIGINT are exact numeric data types, used for storing integer value. Below table lists out the major difference between INT and BIGINT Data Types.

[ALSO READ] TINYINT Vs SMALLINT

INT

BIGINT

Storage Size 4 Bytes 8 Bytes
Minimum Value -2,147,483,648 (-2^31) -9,223,372,036,854,775,808 (-2^63)
Maximum Value 2,147,483,647 (2^31-1) 9,223,372,036,854,775,807 (2^63-1)
Usage Example
DECLARE @i INT
SET @i = 150
PRINT @i

RESULT:
150

DECLARE @i BIGINT
SET @i = 150
PRINT @i

RESULT:
150

Example of Storage Size used by the variable to store the value
DECLARE @i INT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
4

DECLARE @i BIGINT
SET @i = 150
PRINT DATALENGTH( @i)

RESULT:
8

Example of INT out of range value
DECLARE @i INT
SET @i = 2147483648
PRINT @i

RESULT:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

DECLARE @i BIGINT
SET @i = 2147483648
PRINT @i

RESULT:
2147483648

Try to store Negative value
DECLARE @i INT
SET @i = -150
PRINT @i

RESULT:
-150

DECLARE @i BIGINT
SET @i = -150
PRINT @i

RESULT:
-150

[ALSO READ] SMALLINT Vs INT

Selecting the correct data type while creating a table is very critical. In-correct selection of the data type will result in performance and storage issues over the time as the data grows. As in-correct selection of data type results requiring more storage space to store and no. of records stored in each data page will be less. And on top if index is created on such columns, it not only takes the extra space in storing the value in a row in the data page but also requires extra space in the index. Less the no. of records stored in the data page, then to serve the queries Sql Server needs to load more no. of data pages to the memory. So, it is very crucial to select the correct data type while creating table. Hope the above differences will help you in selecting the correct data type while creating the table.

ALSO READ

Leave a Reply

Your email address will not be published.