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:
1 2 3 4 | 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:
1 2 3 4 | INSERT INTO EMPLOYEE_SALARY (EMP_ID, SALARY) SELECT 123, 5500 |
Select the record from the table:
1 2 3 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 | 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:
1 2 3 4 | 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:
1 2 3 | SELECT * FROM BIGINT_DEMO |
So bigint data is capable of storing very large numbers. Bigint data type uses 8 bytes of storage:
1 2 3 4 5 | 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:
1 2 3 4 | 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:
1 2 3 4 | 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.
1 2 3 4 | 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.
1 2 3 | 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:
1 2 3 4 | 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:
1 2 3 | ALTER TABLE EMPLOYEE_DEMO ALTER COLUMN EMP_ID BIGINT |
Let’s run the ‘sp_help’ command to check the column changes:
1 2 3 | sp_help 'EMPLOYEE_DEMO' |
So, the EMP_ID column changed to bigint data type as expected.
Now, let’s drop the table:
1 2 3 | DROP TABLE EMPLOYEE_DEMO |
Create the table again:
1 2 3 4 | CREATE TABLE EMPLOYEE_DEMO (EMP_ID INT NOT NULL, SALARY INT NOT NULL) |
Now we will make the EMP_ID column as primary key:
1 2 3 4 | 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.
1 2 3 | 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:
1 2 3 | ALTER TABLE EMPLOYEE_DEMO ADD EMP_ID_2 BIGINT NOT NULL |
Update the values from the existing int column to the new bigint column:
1 2 3 | UPDATE EMPLOYEE_DEMO SET EMP_ID_2=EMP_ID |
Now drop the primary key constraint from the table:
1 2 3 | ALTER TABLE EMPLOYEE_DEMO DROP CONSTRAINT EMP_ID_PK |
Delete existing int column:
1 2 3 | ALTER TABLE EMPLOYEE_DEMO DROP COLUMN EMP_ID |
Create the constraint for the bigint column:
1 2 3 4 5 6 | CREATE TABLE EMPLOYEE_DEMO ADD CONSTRAINT [PK_EMPLOYEE_DEMO_EMP_ID] PRIMARY KEY CLUSTERED ( EMP_ID_2 ASC ) |
Finally, rename the bigint column:
1 2 3 | 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:
1 2 3 4 5 | CREATE TABLE EMPLOYEE_DEMO_2 (EMP_ID BIGINT NOT NULL, SALARY INT NOT NULL) |
Copy data from the old table to the new table.
1 2 3 4 | INSERT INTO EMPLOYEE_DEMO_2 (EMP_ID, SALARY) SELECT EMP_ID, SALARY FROM EMPLOYEE_DEMO |
Drop the old table:
1 2 3 | DROP TABLE EMPLOYEE_DEMO |
Rename the new table:
1 2 3 | 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)
- Top Power BI Interview Questions and Answers - January 31, 2023
- SQL Subtract Dates with Examples - January 2, 2023
- INT vs BIGINT in SQL Server with Examples - November 22, 2022
Related posts:
- Managing data with SQL Server FILESTREAM tables
- SQL Server 2017: Columnstore Indexes and Trivial Plan
- AWS RDS SQL Server recovery models, backups and restores
- SQL Convert Date functions and formats
- How to build a SQL Server Virtual Lab with AutomatedLab in Hyper-V
FAQs
What is the difference between int and BIGINT SQL? ›
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. bigint fits between smallmoney and int in the data type precedence chart.
When should I use BIGINT? ›BigInt is a new data type intended for use when integer values are larger than the range supported by the Number data type. This data type allows us to safely perform arithmetic operations on large integers, represent high-resolution timestamps, use large integer IDs, and more without the need to use a library.
What is the max size of 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).
Should I use BIGINT for primary key? ›You can use BIGINT as a primary key but with some penalties. BIGINT takes up more space on disk storage than INT and using BIGINT as a primary key (or any index) will add size to the index, perhaps as much as doubling it. This can have a performance impact on searching the index and make it slower to run queries.
How do you know if a number is BigInt? ›The easiest way to determine if you're working with a BigInt is to look for the letter n in your code following a numeric value. The letter n means the value is a BigInt ( 37n ), not a number ( 37 ).
What is BigInt example? ›BigInt is a built-in object in JavaScript that provides a way to represent whole numbers larger than 253-1. The largest number that JavaScript can reliably represent with the Number primitive is 253-1, which is represented by the MAX_SAFE_INTEGER constant.
What is the disadvantage of BIGINT? ›The disadvantage of using BIGINT or INT8 is that they use more disk space than an INTEGER. The actual size depends on the word length of the platform. An INT8 or SERIAL8 value requires 10 bytes of storage. BIGINT and BIGSERIAL values require 8 bytes of storage.
What is the difference between BIGINT and number? ›BigInt values are similar to Number values in some ways, but also differ in a few key matters: A BigInt value cannot be used with methods in the built-in Math object and cannot be mixed with a Number value in operations; they must be coerced to the same type.
Can you insert INT into BIGINT? ›ALTER COLUMN as an online operation using the WITH (ONLINE=ON) syntax in Enterprise Edition. This wonderful syntax now allows you to alter a column from int to bigint without causing major blocking.
What is the difference between number and BIGINT? ›BigInt values are similar to Number values in some ways, but also differ in a few key matters: A BigInt value cannot be used with methods in the built-in Math object and cannot be mixed with a Number value in operations; they must be coerced to the same type.
What is the difference between int and BIGINT in mysql ID? ›
The only difference is the range of the type. INT is a 32-bit long while BIGINT is 64-bit long, therefore it can store much larger numbers like 123456789123456789 (which cannot be stored as INT ).
What is the difference between int and BIGINT storage in mysql? ›The int type takes 4 byte signed integer i.e. 32 bits ( 232 values can be stored). The BigInt type takes 8 byte signed integer i.e. 64 bits (264 values can be stored).
What does BIGINT mean in SQL? ›What is a BigInt? The BigInt data type in SQL Server is the 64-bit representation of an integer. It takes up 8 bytes of storage. It can range from -2^63 (-9,223,372,036,854,775,808) to 2^63 (9,223,372,036,854,775,807). Two raised to the power of sixty-three is about nine quintillion, a very big number.