Difference between COUNT and BIG_COUNT from the angle of execution plan

We all know when we need to find the the count of rows in a table which exceed the int datatype range, we use BIG_COUNT instead of COUNT, which has return type bigint unlike int in case of COUNT.

Let’s see from the prospective of execution plan, how it differ in plan

select count(*) from Production.ProductCategory pc 

Lets see the text plan to see, what optimizer in detail did to execute the above query.

SET STATISTICS PROFILE ON

select count(*) from Production.ProductCategory pc 

Again a very simple plan, lets go through with each step to see how optimizer executes the query. First step, all rows are read from NonClustered Index Scan, then Stream Aggregate counts the number of rows in the record set and place the record count in Expr1002. Then Compute Scalar Operator takes the record count stored in Expr1002 and convert it to INT Datatype and kept the result in Expr1001.

The output of the Stream Aggregate operator  is a BigInt, but as we know the COUNT aggregate function returns value of type INT, so it use Compute scalar to convert result of count in INT data type. You may remember that, in order to count BigInt values, you should use the COUNT_BIG function. If you change the query above to use the COUNT_BIG, then you will see that the plan no longer uses the compute scalar operator

Lets check out the same query with BIG_COUNT.

select count_big(*) from Production.ProductCategory pc 

 

Here we can see there is no Compute scalar operator in the execution plan. Lets see now whether this plans makes any difference in cost compared to query using COUNT.

Here if we compare the final tree cost the query using BIG_Count has 0.0032893 where as query using COUNT has  0.0032893. So it came out as same. So it makes no or minimal cost change while using count vs big_count.

Conclusion: BIG_COUNT don’t use compute Scalar Operator because the Stream Aggregate operator of COUNT returns result in BIG INT datatype where as in case of COUNT function, since it has return type of INT, the compute scalar operator is used to convert the result returned from  Stream Aggregate from big int to int.

 

SQL Server with a Tea glass makes a perfect combination to deal with darkness :)

Post Reference: Vikram Aristocratic Elfin Share