Arindam Mondal
Using sp_help command showing column data type, EMP_ID columns is now bigint type

INT vs BIGINT in SQL Server with Examples

November 22, 2022 by

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:

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:

Select the record from the table:

Select EMPLOYEE_SALARY table

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:

Showing length value 4 for the int data type

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:

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:

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 BIGINT_DEMO table

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

Showing length value 8 for the int data type

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:

Converting whole numbers can result decimal values if it is greater than 2,147,483,647

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:

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.

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

Select INT_CHECK table

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:

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

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

Using sp_help command showing column data type, EMP_ID columns is now bigint type

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

Now, let’s drop the table:

Create the table again:

Now we will make the EMP_ID column as primary key:

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

Error message showing while using ALTER TABLE command on EMP_ID column to change its data type to bigint type

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:

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

Now drop the primary key constraint from the table:

Delete existing int column:

Create the constraint for the bigint column:

Finally, rename the bigint column:

Showing warning message while renaming column EMP_ID_2 to EMP_ID from the table EMPLOYEE_DEMO

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:

Copy data from the old table to the new table.

Drop the old table:

Rename the new table:

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.

Arindam Mondal
Latest posts by Arindam Mondal (see all)
T-SQL

About 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)

168 Views