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:
Hope you enjoyed this blog post, please share it with your friends!
SQLZealots 