Let me answer you first – there is no difference between Count and Count_Big in terms of functionality. They are almost the same. The only difference is the datatype which they return and index creation on view.

Let us discuss both the difference here.

Difference Between Count and Count_Big

DataType

When you use Count(*) function it returns data type as INT (range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) and storage 4 bytes) whereas when you use COUNT_BIG(*) it returns the datatype as the BIGINT (range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) and storage 8 bytes).

View and Index

Another major difference between both the datatype is when you create an index on the view. You can not create clustered index on view when you have used COUNT(*) in the view. However, you can create a clustered index on the view if you have use COUNT_BIG(*) in the view. This is why I see lots of people have a habit of using COUNT_BIG(*) when they are using the aggregation function in the view instead of COUNT(*).

Well, that’s it for today. A short blog post.

Let me know if you are interested to know more about this topic like a bitwise puzzle and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)