COUNT and COUNT_BIG functions in SQL Server

Todays post, we will quickly see the two important functions count and count_big in SQL Server. Both functions return the number of records in a table. There are lots of interesting facts with count function and we will try to cover most of things, if you have any more points, feel free to share your thoughts as a comment.

Return types of count and count_big

As mentioned earlier, these two functions returns the number of records, however these two differ in return datatype, COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Let us quickly see with a system procedure (sp_describe_first_result_set) to understand the return datatype as below:

Count and column name and non column name as parameter

The parameter value makes the difference on column name and non column name expression of the table involved. The below screen shot explains you the evaluation on the column name while we provide a valid column name as parameter otherwise, irrespective of the expression value, it just do not evaluate.

As a simple example, Select 1/0 always produces an error in SQL Server, but 1/0 as a parameter to count or count_big function does not evaluate it.

Count and NULL values in table

This is another interesting point to be aware in SQL Server NULL value column in a table plays a big role with count functions. If the parameter to a count function is a column name and the column is a nullable one, it is extremely important to be noted the NULL values will not be considered to count function.

See Also:

https://blog.sqlauthority.com/2010/09/21/sql-server-count-not-allowed-but-count_big-allowed-limitation-of-the-view-5/

Hope you enjoyed this blog post, please share it with your friends!

One thought on “COUNT and COUNT_BIG functions in SQL Server”

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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