INT vs BIGINT in SQL Server with Examples

SQL Server provides int, bigint, smallint and tinyint data for storing exact-number data. In this article, we will focus on int and bigint data types.

Introduction

SQL Server data types define the kind of data that can be stored in the table: integer data, character data, monetary data, date and time data, binary strings, and so on. Each table has columns and each column must have a name and data type associated with it. SQL Server provides a list of system data types that defines all types of data that can be used with SQL Server. If your application requires a custom data type, then you can also create your data type in Transact-SQL or the Microsoft .Net Framework. In this article, we will compare int vs bigint data type.

Sometimes you may have to compare two expressions that have different data types, collations, precisions, scales, or lengths which are combined by an operator, the final results are determined by the below properties:

The rules of the data type precedence to the input data types in the expressions are applied to the results. The collation of the result is based on the rules of collation precedence and it is applicable for the result data type char, varchar, text, nchar, nvarchar, or ntext. The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. SQL Server provides different categories of data types: Exact numerics, Unicode character strings, Approximate numerics, Binary strings

Date and time, Character strings, and other data types. While working with numeric data type it is important to understand int vs bigint data type.

While working on SQL Server database development, it is important to know the different data types available to use in Microsoft SQL Server. A data type is essentially a constraint, meaning that the data type you choose limits the kind of values you can store. For example, you should not store a person’s age in a datatype like BIGINT because it is used to store large numbers. A SQL developer must know the importance of int vs bigint data type. Different data types require different storage sizes to store the data so it is important to choose the right data type for your data.

SQL Server provides int, bigint, smallint and tinyint data for storing exact-number data. In this article, we will focus on int vs bigint data type.

INT

The integer data type is the most commonly used data type available in SQL Server. So, it is important to understand the Int data type. int data type comes in the middle among integer data types. It is neither the largest nor the smallest type. That is the reason it is acceptable in many scenarios.

Let’s create a sample table using the int data type:

CREATE TABLE EMPLOYEE_SALARY

(EMP_ID INT, SALARY INT)

In the above example, we are creating a table EMPLOYEE_SALARY which has two columns EMP_ID and SALARY. Now, let’s insert a sample record into the table:

INSERT INTO EMPLOYEE_SALARY (EMP_ID, SALARY)

SELECT 123, 5500

Select the record from the table:

SELECT * FROM EMPLOYEE_SALARY

INT data type range is -2,147,483,648 to 2,147,483,647. It is the most suitable data type to store the whole numbers for most use cases.

An integer data use 4 bytes of storage memory. We can check it by using the DATALENGTH function:

DECLARE @i INT

SET @i=100

PRINT DATALENGTH(@i)

In the above example, we declared a variable I with the integer data type, assigned the variable value to 100 then printing the variable length using the data length function. We got the value 4 from the print messages, so int data use 4 bytes of storage memory.

BIGINT

In the above section, we discussed integer data types. Now let’s talk about bigint data types. So that you can understand int vs bigint datatype. If you need to store very big numbers, then the bigint data type is the right option. The data type can store big numbers as much as 9 quintillions (If you are not sure about how big it is, the list goes million, billion, trillion, then quadrillion). I practically have not seen much use of bigint data type.

Let’s create a new table to understand bigint data type:

CREATE TABLE BIGINT_DEMO

(PLANET_NAME VARCHAR (20),

PLANET_AGE BIGINT)

In the above image, we created a new table BIGINT_DEMO which has two columns, PLANET_NAME with VARCHAR data type and PLANET_AGE with BIGINT data type

Now, let’s try inserting a sample record:

INSERT INTO BIGINT_DEMO

SELECT 'EARTH','4543000000'

We have inserted the value ‘EARTH’ and ‘4543000000’ to the columns PLANET_NAME and PLANET_AGE respectively.

Let’s select the record from the table:

SELECT * FROM BIGINT_DEMO

So bigint data is capable of storing very large numbers. Bigint data type uses 8 bytes of storage:

DECLARE @i BIGINT

SET @i=100

PRINT DATALENGTH(@i)

In the above example, we declared a variable i with the integer data type, assigned the variable value to 100 then printing the variable length using the data length function. We got the value 8 from the print messages, so bigint data use 8 bytes of storage memory. This is one of the major difference between int vs bigint datatype.

Bigint data type range is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).

Converting whole numbers

Let’s discuss another difference between int vs bigint data type. While converting the whole number if the number is greater than 2,147,483,647 then SQL Server converts it to decimal data type, not bigint data type:

SELECT 2147483647 / 3 AS Value1,

2147483649 / 3 AS Value2;

In the above example, we have two numbers 2147483647 and 2147483649, and divide both numbers by 3. But in the result, the second number is converted to decimal data type.

Integer rounds decimal points

Now, we will discuss another topics for int vs bigint data type. If we have a table with an integer column and we are trying to insert a decimal value, then the number will be rounded to an integer. To understand this behaviour, let’s create a sample table:

CREATE TABLE INT_CHECK

(ID INT,AMOUNT INT)

In the above query, we are creating a sample table INT_CHECK which has two columns ID with INT data type and Amount with INT data type.

Now inserting sample values 121 and 1234.4243 to the columns ID and Amount respectively.

INSERT INTO INT_CHECK (ID, AMOUNT)

SELECT 121,1234.4243

Let’s select the table. Note the values in the AMOUNT column rounded to integer data.

SELECT * FROM INT_CHECK

So, if you have an integer column data type in the table and trying to insert a decimal value then the value will be truncated to the whole number, you must take care of the incoming application data so that it matches with the inserted values in the table.

Convert INT to BIGINT Column

While working with a database table you may need to change column data types. In this section, we will discuss converting int column data type to bigint data type to understand better int vs bigint data type. We can convert the existing int column to a bigint column by using alter command.

Let’s create a demo table to explain this option:

CREATE TABLE EMPLOYEE_DEMO

(EMP_ID INT NOT NULL, SALARY INT NOT NULL)

We can use ALTER TABLE command to change the INT column to the BIGINT column:

ALTER TABLE EMPLOYEE_DEMO ALTER COLUMN EMP_ID BIGINT

Let’s run the ‘sp_help’ command to check the column changes:

sp_help 'EMPLOYEE_DEMO'

So, the EMP_ID column changed to bigint data type as expected.

Now, let’s drop the table:

DROP TABLE EMPLOYEE_DEMO

Create the table again:

CREATE TABLE EMPLOYEE_DEMO

(EMP_ID INT NOT NULL, SALARY INT NOT NULL)

Now we will make the EMP_ID column as primary key:

ALTER TABLE EMPLOYEE_DEMO

ADD CONSTRAINT EMP_ID_PK PRIMARY KEY (EMP_ID)

Let’s try to change the EMP_ID column data type to BIG INT.

ALTER TABLE EMPLOYEE_DEMO ALTER COLUMN EMP_ID BIGINT

But we are getting an error. So, if the column is the primary key then we cannot change the column data type.

There is a solution to the above problem. Let’s discuss it below as part of int vs bigint data type.

Create a new bigint column in the same table:

ALTER TABLE EMPLOYEE_DEMO ADD EMP_ID_2 BIGINT NOT NULL

Update the values from the existing int column to the new bigint column:

UPDATE EMPLOYEE_DEMO SET EMP_ID_2=EMP_ID

Now drop the primary key constraint from the table:

ALTER TABLE EMPLOYEE_DEMO DROP CONSTRAINT EMP_ID_PK

Delete existing int column:

ALTER TABLE EMPLOYEE_DEMO DROP COLUMN EMP_ID

Create the constraint for the bigint column:

CREATE TABLE EMPLOYEE_DEMO ADD CONSTRAINT [PK_EMPLOYEE_DEMO_EMP_ID] PRIMARY KEY CLUSTERED

(

EMP_ID_2 ASC

)

Finally, rename the bigint column:

EXEC sp_rename 'EMPLOYEE_DEMO.EMP_ID_2','EMP_ID','COLUMN'

But the above solution may create an issue. If you are using column order in your application then it may cause an error. You can also view the warning message above. There is another solution to this issue. Let’s discuss it to understand better int vs bigint datatype:

Create a new table with a similar structure to the old table with a new bigint datatype in the id column:

CREATE TABLE EMPLOYEE_DEMO_2

(EMP_ID BIGINT NOT NULL,

SALARY INT NOT NULL)

Copy data from the old table to the new table.

INSERT INTO EMPLOYEE_DEMO_2 (EMP_ID, SALARY)

SELECT EMP_ID, SALARY FROM EMPLOYEE_DEMO

Drop the old table:

DROP TABLE EMPLOYEE_DEMO

Rename the new table:

EXEC sp_rename 'EMPLOYEE_DEMO_2', 'EMPLOYEE_DEMO'

Conclusion

In this article, we discussed INT vs BIGINT datatype, and their usage, and compared both the data type. You need to choose the appropriate data type based on your application’s requirements.

Author
Recent Posts

Arindam Mondal

Arindam is an experienced and highly motivated IT enthusiast who likes to leverage his technical expertise to address critical business needs. A self-guided learner who loves to learn every single day. He loves to indulge in cultural diversity and travel to newer destinations.

Arindam has rich experience in Azure solution implementation and support activities for large enterprise clients, having worked on multiple developments and enhancement projects. He is
currently based in India and is working as a Technical Lead for a leading MNC Company.
A few significant facts about his career:
* Has over 10.7 years of IT experience in Microsoft platforms.
* Has around 8 years of experience in SQL database development (T-SQL/Performance tuning/CDC) and ETL-SSIS.
* Has 5 years' experience in Azure Environment (Azure Data Factory, Azure Data Lake Storage, Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics)

Latest posts by Arindam Mondal (see all)

Understanding the SQL IN operator with examples - March 19, 2024
An introduction to PostgreSQL Data Types with examples - September 15, 2023
Understanding Substring in Oracle SQL - March 22, 2023