4 Ways to Count Rows in SQL Server Table with Pros and Cons

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 SQL Complete SSMS Add-in. 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.

Tags: count rows, sql count, sql functions, sql server Last modified: August 08, 2022