SQL Server BIGINT Data Type

BIGINT is an integer data type that can store integer values larger than INT. In SQL Server, the BIGINT data type is stored in an 8-byte memory space, and its value range is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Syntax

The syntax of the BIGINT data type is as follows:

BIGINT

Use Cases

Since the BIGINT data type can store larger integer values, it is commonly used in applications that require handling large amounts of data. Here are some common scenarios for using the BIGINT data type:

• Storing large ID numbers
• Storing timestamps
• Storing numerical data types

Examples

Here are two examples demonstrating how to use the BIGINT data type in SQL Server.

Example 1

Suppose we have an employees table that contains the ID and age of each employee. To better manage this table, we want to assign a unique ID number to each employee. Since we need to store a large number of ID numbers, we will use the BIGINT data type to store them.

The following statement creates a table with a BIGINT column:

CREATE TABLE employees (
  id BIGINT PRIMARY KEY,
  age INT
);

Insert some test data:

INSERT INTO employees (id, age) VALUES
(100000000000001, 25),
(100000000000002, 30),
(100000000000003, 35),
(100000000000004, 40);

In the above example, we created an employees table and used the BIGINT data type to store the ID number of each employee. We also inserted some data into the table so that we can test if it works as expected.

Example 2

Suppose we have a transactions table that contains the ID, amount, and date of each transaction. We want to calculate the total transaction amount for each customer. Since the transaction amount can be very large, we will use the BIGINT data type to store them.

Create the transactions table:

CREATE TABLE transactions (
id INT,
customer_id INT,
amount BIGINT,
date DATE
);

Insert some test data:

INSERT INTO transactions (id, customer_id, amount, date) VALUES
(1, 1, 100000000000001, '2022-01-01'),
(2, 2, 100000000000002, '2022-01-02'),
(3, 1, 100000000000003, '2022-01-03'),
(4, 3, 100000000000004, '2022-01-04'),
(5, 1, 100000000000005, '2022-01-05');

In the above example, we created a transactions table and used the BIGINT data type to store the amount of each transaction. We also inserted some data into the table so that we can test if it works as expected.

Now, we can use the following query to calculate the total transaction amount for each customer:

SELECT customer_id, SUM(amount) AS total_amount
FROM transactions
GROUP BY customer_id;

After running the above query, we will get the following results:

In the above results, we can see the total transaction amount for each customer, which is stored using the BIGINT data type.

Conclusion

The BIGINT data type in SQL Server can be used to store integer values larger than INT. It is commonly used in applications that deal with large amounts of data, such as storing large ID numbers, timestamps, and numerical data types. In this article, we have provided two examples demonstrating how to use the BIGINT data type to store data in SQL Server and how to query and manipulate this data.