Home > SQL Server, T-SQL > Row – Count Vs Count_Big (T-SQL) – SQL Server

Row – Count Vs Count_Big (T-SQL) – SQL Server


Counting number of items in a table is the greatest invention by the developers to look for any change in the table. For instance, ETL developers run this command to compare the row count of a table before and after upload.

There are two functions available in SQL Server for counting the rows in a table. They are Count() and Count_Big(). These functions do the same work except the return value.

Count() returns an integer data type. The maximum value return by this function is 2,147,483,647

Count_Big() returns a big integer data type. The maximum value return by this function is  9,223,372,036,854,775,807

Function Syntax

Both functions has same syntax.

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

ALL is the default keyword. If you are using a column name in count function ALL can be prefixed with the column name. It excludes NULL value and includes duplicate values.

Example

Select Count(ALL <Column name>) from <Table Name>

or

Select Count(<Column name>) from <Table Name>

If the column contains null value then we will get a warning message as given below

Warning: Null value is eliminated by an aggregate or other SET operation.

Distinct

It gives distinct count value and it excludes both Null values and duplicate values from the count.

If the column contains null value then we will get a warning message as given below

Warning: Null value is eliminated by an aggregate or other SET operation.

Example

Select Count(Distinct <Column name>) from <Table Name>

*

It represents everything including Null value and it does not require any expression

Example

Select Count(*) from <Table name>

Count_Big() does have the same syntax as Count(). We have to remember that the max value return by these functions are different. To get the accurate result we have to choose appropriate function in our application.

Example

Printing row count of all tables in a database. In this example, I am using an undocumented function. So, use this example at your own risk.

Use <Database name>

EXEC sp_MSforeachtable ‘Select count(*) as [?] from  ? ‘

Frequently Asked Question

what is the different between count(*) and count(1) or count(numeric(38,0) ). There is no differences between those two. It will return the same result. Whereas, if you use column name then it will count only non-null value.

Thanks for reading.

Advertisements
  1. No comments yet.
  1. April 21, 2013 at 11:22 am

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: