COUNT_BIG function in SQL Server

COUNT_BIG is similar to the COUNT function. The only difference between these functions is the data types of their return values. COUNT_BIG always returns a value of the bigint data type. COUNT always returns a value of the int data type.

When used without the OVER and ORDER BY clauses, COUNT_BIG is a deterministic function. It means it always returns the same result when called with a specified set of input values and the same database state.

When combined with the OVER and ORDER BY clauses, COUNT_BIG is nondeterministic. It means that, even though the database state they access remains the same, they may produce different results each time they are called with a certain set of input values.

Let’s have a look at how it works.

Please keep in mind that the query below will insert 100,000 rows and will take 1.3 minutes to run on a laptop. This execution isn’t necessary. You can use the function on any table.

CREATE TABLE tbStudent(bigID BIGINT, StudentName VARCHAR(50))

SELECT @bigid = 2200000000
WHILE @bigid >=2200000000 and @bigid <= 2200100000
INSERT INTO tbStudent VALUES(@bigid, ‘Student_’ + CONVERT(VARCHAR(15), @bigid))
SELECT @bigid = @bigid + 1

Now, let’s use COUNT_BIG function.

I hope you found this post to be informative.

To receive notifications of new posts, please subscribe.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s