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 was causing issues. That query populated the count of the rows and stored the value in a local variable. It 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.

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

Сount Rows in All Tables in Database

In many cases, specialists working with SQL Servers need to define the number of rows in one or several database tables. This is a common task for DBAs, testers, and developers and a frequent question during the job interviews. You need to understand the ways to count rows in all tables in database, as it is necessary for planning and performance analysis. What if some table has grown enormously, causing the performance problems?

Hence, you need to count rows in all tables in database. A popular approach is when you use a loop for every table, one by one, and thus select all tables. Unfortunately, it would hardly be suitable if you deal with a database containing many large tables. Using a loop will consume many hours of your time, and time is the most precious thing, But, of course, there are other ways to count rows in all tables.

For instance, you can appeal to the aggregate functions. Or, you can use the SSMS functionality to prepare a fast query. It can show all tables and help you to count the numbers of rows in them all. Having predefined scripts to count rows in all tables is helpful, as it allows you to reduce the time and effort paid. And, there are other efficient ways to count the number of rows in a table of SQL Server. This article will describe the most reliable methods for you to choose.

However, it is important to remember that there are no perfect solutions to count rows in all tables in database. Depending on the method to use, you may get more or less precise results. There will always be catches that you should consider. Still, when you are aware of them and know how to overcome the obstacles, you can select all tables and count all rows and other items in them. 

Take a look at the most functional approaches:

  • Use COUNT() function
  • Combining SQL Server catalog views
  • Using sp_spaceused stored procedure
  • Using SQL Server Management studio

And now, we’ll dive deeper and explore each approach with its pros and cons.

Use SELECT COUNT (*) or COUNT(1) to Count Rows in a Table

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: 

The output of the query to get the row count

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:

The output of query using COUNT(1)

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 sys.tables, sys.indexes  and sys.partitions 

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 output of the query to get the count of rows using SQL Server catalog views with the dynamic management views

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 Procedure for SQL Server Database 

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:

The output of the query using the sp_spaceused procedure. Along with the rows count it provides the more details

How to Show Line Numbers in 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

To get the rows count of the table, we can use SQL Server Management Studio

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

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.

Nisarg Upadhyay

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.

Leave a Reply

Your email address will not be published. Required fields are marked *