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))
GO
DECLARE @bigid BIGINT
SELECT @bigid = 2200000000
WHILE @bigid >=2200000000 and @bigid <= 2200100000
BEGIN
INSERT INTO tbStudent VALUES(@bigid, ‘Student_’ + CONVERT(VARCHAR(15), @bigid))
SELECT @bigid = @bigid + 1
END
GO
Now, let’s use COUNT_BIG function.
SELECT COUNT_BIG(bigID) FROM tbStudent
I hope you found this post to be informative.
To receive notifications of new posts, please subscribe.
Big Data and SQL 
2 comments