What are 3 ways to get a count of the number of records in a table in SQL?

There are several ways to get a row count in MySQL. Some database management products provide database statistics like table sizes, but it can also be done using straight SQL. In today’s tip, we’ll use the native COUNT() function to retrieve the number of rows within one table or view within a MySQL database. In part 2, we’ll learn how to obtain a row count from multiple tables, or even from all of the tables within a database.

You probably already know that the COUNT() function returns the number of rows in a table. But there’s a little more to it than that, as the COUNT() function can be utilized to count all rows in a table or only those rows that match a particular condition. The secret is in the function signatures, of which there are several forms: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression).

In each case, COUNT() returns a BIGINT that contains either the number of matching rows, or zero, if none were found.

To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.

A statement like the one above that invokes the COUNT(*) function without a WHERE clause or additional columns, will perform very fast on MyISAM tables because the number of rows is stored in the table_rows column in the tables table of the information_schema database.

For transactional storage engines such as InnoDB, storing an exact row count is problematic because InnoDB does not keep an internal count of rows in a table. If it did, concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction. What that means is that running a query with COUNT(*) during a heavy workload could result in slightly inaccurate numbers.

Passing nothing to COUNT() executes the COUNT(expr) version of the function, but sans parameter. Invoking COUNT() in that way only returns rows which are not comprised of NULL values. For example, say that we had a simple table called code_values:

Selecting COUNT() from the table would return 2, even though there are 4 rows:

+---------+
| COUNT() |
+---------+
| 2       |
+---------+

Note that this version of the COUNT() function is rarely used because NULL rows should not be an issue in normalized databases, a condition that could only happen if the table didn't have a primary key. In most cases, COUNT(*) will work just fine.

Of course, COUNT(expr) does accept proper expressions. Here’s another query that fetches NULL and non-NULL rows alike:

Counting Non-null Values

The COUNT(expr) version of the COUNT function also accepts individual column names, the effect of which is that COUNT(column_name) will return the number of records where column_name is not NULL. Hence, the following SELECT query would fetch the number of rows where the description column contained a non-NULL value:

In Part 2 we’ll learn how to use the COUNT(DISTINCT expression) signature as well as how to obtain a row count from multiple tables.

Recently, I was working on a database performance improvement project. One stored procedure there was causing issues. In its code, a query populated the Count of the rows and stored the value in a local variable. That query was scanning a large table. Due to that, resource utilization became significantly higher. To fix the issue, we removed the faulty code and used the SQL Server catalog views to generate the Count of rows of the table.

There are various ways to count the number of rows in a table of SQL Server. This article will describe them for you to always choose the right way to do it.

We can get the Count of rows of the table with any of the following methods:

1. Use COUNT() function.
2. Combining SQL Server catalog views.
3. Using sp_spaceused stored procedure.
4. Using SQL Server Management studio.

Let us dig deeper.

Get row count using COUNT(*) or Count(1)

We can use the COUNT(*) or COUNT(1) function – the results generated by these two functions are identical.

To get the row count, let us first run the query using COUNT(*). For demonstration purposes, I have set the value of STATISTICS IO as ON.

USE wideworldimporters 
go 

SELECT Count(*) 
FROM   tblcustomer 
go  

Output: 

IO statistics:

Table 'tblCustomer'. Scan count 1, logical reads 691, physical reads 315, page server reads 0, read-ahead reads 276, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

As you can see, the SQL Server has to perform 691 logical reads to satisfy the result.

Now, let us run the query using COUNT(1):

USE wideworldimporters 
go 

SELECT Count(1) 
FROM   tblcustomer 
go  

Output:

IO Statistics:

Table 'tblCustomer'. Scan count 1, logical reads 691, physical reads 687, page server reads 0, read-ahead reads 687, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Again, SQL Server must perform 691 logical reads to satisfy the result.

We should mention that there is an opinion that the Count (1) is faster than the Count (*) function. However, as you can see in the above examples, the result sets and IO statistics are the same. Therefore, you can use any method to generate the row count of tables.  

Pros:

The COUNT function populates an accurate number of rows from the table.

Cons:

When you execute the COUNT function, it places a lock on the table. Other queries accessing the table have to wait until the result is generated. If you are working on a busy system with a table having millions of rows, you’d better avoid running the COUNT function during business hours, unless you have to populate the exact row count of the table.

Combining SQL Server catalog views

We can use SQL Server catalog views with the following dynamic management views:

1. sys.tables – populates the list of tables.
2. sys.indexes – populates the list of indexes of the table.
3. sys.partitions – populates the rows of each partition.

To get the count of rows, run the following script:

SELECT a.NAME, 
       c.NAME, 
       Sum(b.rows) 
FROM   sys.tables a 
       INNER JOIN sys.partitions b 
               ON a.object_id = b.object_id 
       INNER JOIN sys.indexes c 
               ON b.index_id = c.index_id 
                  AND b.object_id = c.object_id 
WHERE  a.object_id = Object_id('tblCustomer') 
       AND c.index_id < 2 

Output:

The query populates the table name, index name, and total rows in all partitions.

Now, let us review the IO Statistics:

Table 'syssingleobjrefs'. Scan count 3, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysrowsets'. Scan count 2, logical reads 14, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

As you can see, the query performs only 30 logical reads.

Pros:

This approach is faster than the COUNT function. It does not acquire a lock on the user table, so you can use it in a busy system.

Cons:

The method populates an approximate Count of rows. In the Microsoft documentation of sys.partitions, you can see that the rows column brings the approximate number of rows for the partitions.

Thus, if you are looking for a query that brings the result faster than the COUNT function, you can use this one. However, the result might be inaccurate.

Use sp_spaceused

The sp_spaceused procedure along with the rows count provides the following details:

1. Name – the Table Name
2. Rows – the Count of the rows in a table.
3. Reserved – the total reserved space for a table.
4. Data – the total space used by the table.
5. Index_size – the total space used by the index.
6. Unused – the total reserved space for a table that is not used.

The syntax is:

EXEC Sp_spaceused 'database_name.schema_name.table_name' 

The query:

EXEC Sp_spaceused 'WideWorldImportors.dbo.tblCustomer' 

Output:

Use SQL Server Management Studio

To get the rows count of the table, we can use SQL Server management studio.

Open SQL Server Management studio > Connect to the database instance > Expand Tables > Right-click on tblCustomer > Properties

In the Table Properties window, click on Storage. You will see the Row count value on the right:

Another option to get the number of rows in a table comes with the . With this enhancement, you can see the estimated number of rows in a hint when you hover the mouse over a table name in the Object Explorer window. This way, you can get the necessary data in a visual mode without any additional efforts.

Conclusion

This article explained different approaches to calculating the total number of rows of the table, in particular:

1. Using the COUNT function.
2. Combining different catalog views.
3. Using sp_spaceused stored procedure.
4. Using SQL Server Management studio.

There is no need to stick to one method only. Each variant has its specificities, and you can apply the one that is the best-suitable in your situation.

About the Author / Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

What are 3 ways to get a COUNT of the number of records in a table?

How can we get COUNT of the number of records in a table?

Which function is used to COUNT the number of records?