Blog

“How many rows exist in a table?”

It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of them are perfect!

COUNT(*) or COUNT(1)

The seemingly obvious way to get the count of rows from the table is to use the COUNT function. There are two common ways to do this – COUNT(*) and COUNT(1). Let’s look at COUNT(*) first.

SELECT COUNT(*)
FROM dbo.bigTransactionHistory;

The STATISTICS IO output of this query shows that SQL Server is doing a lot of work! Over 100,000 logical reads, physical reads, and even read-ahead reads need to be done to satisfy this query.

(1 row(s) affected)
Table 'bigTransactionHistory'. Scan count 5, logical reads 132534, 
physical reads 3, read-ahead reads 131834, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.

Looking at the execution plan, we can see an Index Scan returning over 31 million rows. This means that SQL Server is reading every row in the index, then aggregating and counting the value – finally ending up with our result set. The cost of this query? 123.910000.

count 1

The query results: 31,263,601 rows.

count 2

Now, let’s look at the behavior of COUNT(1).

SELECT COUNT(1)
FROM dbo.bigTransactionHistory;

We can see from STATISTICS IO that we have a large number of logical reads – over 100,000.

(1 row(s) affected)
Table 'bigTransactionHistory'. Scan count 5, logical reads 132531, 
physical reads 3, read-ahead reads 131849, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.

The execution plan again shows an index scan returning over 31 million rows for processing. The query cost is the same, 123.910000.

count 3

The results here are the same – 31,263,601 rows.

count 4

The benefit of using COUNT is that it is an accurate indicator of exactly how many rows exist in the table at the time query processing begins. However, as the table is scanned, locks are being held. This means that other queries that need to access this table have to wait in line. This might be acceptable on an occasional basis, but I frequently see applications issuing these types of queries hundreds or thousands of times per minute.

sys.tables + sys.indexes + sys.partitions

We can join several SQL Server catalog views to count the rows in a table or index, also. sys.tables will return objects that are user-defined tables; sys.indexes returns a row for each index of the table; and sys.partitions returns a row for each partition in the table or index. I am going to query for the table ID, name, and count of rows in all partitions.

DECLARE @TableName sysname
SET @TableName = 'bigTransactionHistory'

SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @TableName
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

The output of STATISTICS IO here shows far fewer reads – 15 logical reads total.

(1 row(s) affected)
Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysidxstats'. Scan count 2, logical reads 4, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The execution plan is more complex, but much less work – the query cost here is 0.0341384.

count 5

The results of the query are also the same – 31,263,301.

count 6

The benefits of using this method are that the query is much more efficient, and it doesn’t lock the table you need the count of rows for.

However, you need to be cautious when counting the rows on a table that is frequently inserted into or deleted from. The TechNet documentation for sys.partitions.rows says it indicates the “approximate number of rows for this partition”. How approximate? That information isn’t documented. Understand, though, that if you use this method, you potentially sacrifice up-to-the-moment accuracy for performance.

sys.dm_db_partition_stats

A third option is to use the dynamic management view sys.dm_db_partition_stats. This returns one row per partition for an index.

DECLARE @TableName sysname
SET @TableName = 'bigTransactionHistory'

SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(@TableName)
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);

The STATISTICS IO output of this query is even lower – this time, only two logical reads are performed.

(1 row(s) affected)
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The execution plan is less complex than our second example involving the three system views. This query also has a lower cost – 0.0146517.

count 7

The query results are the same as the previous examples – 31,263,301 rows.

count 8

Using this DMV has the same benefits as the system views – fewer logical reads and no locking of the target table. The query is also simpler to write, involving only one object.

But again, the TechNet documentation for sys.dm_db_partition_stats.row_count says it is “the approximate number of rows in the partition”, and when this information is updated is not documented. Here, you are also potentially sacrificing accuracy for performance.

Time to do some digging

The questions that you need to work with the business to answer are, “How up-to-date must the row count be? What is the business purpose? How often do you insert into or delete from that table, and how often do you count the rows?” If the accuracy of the row count is crucial, work to reduce the amount of updates done to the table. If performance is more important, and the row count could be approximate, use one of the system views.

↑ Back to top
  1. Excellent article on a simple task most of us take for granted, thanks. In my shop though most developers don’t have access to the system views but they still want to get the number of rows. I suggest that they use sp_spaceused because it gets the row count from dm_db_partition_stats and avoids the big costly scans.

  2. Great artificial. Just thought that Id mention that your sql examples have been messed up by xml code formatting.

  3. One last thing. Why is it necessary to perform a sum on row_count? Surely the table will either be on the heap or not, it cant be both can it? I am assuming that you meant to be looking for index_id’s < 2.

    DECLARE @TableName sysname
    SET @TableName = 'bigTransactionHistory'

    SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
    FROM sys.dm_db_partition_stats
    WHERE object_id = OBJECT_ID(@TableName)
    AND index_id < 2
    GROUP BY OBJECT_NAME(object_id);

    • Ooops! Fixed the code samples – thanks for catching that.

      I’m summing the count because if the table is partitioned, you’d receive a row for each partition. This example is designed to get the count of the entire table.

  4. DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS
    SELECT OBJECT_NAME(id), rows FROM sysindexes WHERE indid < 2

    This used to work on sql2000 too.

    • It works in all versions of SQL Server, but even Microsoft says not to run it frequently – it can take a long time on large tables.

  5. Nice!! Quick question… How do I incorporate the where clause to use it with the sys views? The COUNT clauses I have seen usually include joins and where statements but I’m not sure how to fit it in this approach.

  6. Why is sys.indexes needed in this?

    DECLARE @TableName sysname
    SET @TableName = ‘bigTransactionHistory’

    SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows
    FROM sys.tables TBL
    INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
    INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
    AND PART.index_id = IDX.index_id
    WHERE TBL.name = @TableName
    AND IDX.index_id < 2
    GROUP BY TBL.object_id, TBL.name;

  7. Pingback: (SFTW) SQL Server Links 21/02/14 • John Sansom

  8. Whoops! Good to know, now running and try in production…XD…just joking, but it’s an interesting approach I never saw before or applied myself, surely will use it sooner or later.

  9. Hello!

    Years ago, I wrote this piece on the alternatives to SELECT COUNT(*) [http://beyondrelational.com/modules/2/blogs/77/posts/11297/measuring-the-number-of-rows-in-a-table-are-there-any-alternatives-to-count.aspx] – I did not tie it up to the execution plans, however.

    The execution plan analysis in this article helps understand the impact of each of these options in a much greater detail.

    Thank-you for the post!

  10. If you need the row count quite frequently, an indexed view might also offer a way to bring down the query costs of inferring the number of rows, while adding a little extra cost to all data modification operations.

  11. Pingback: Back to Basics!! | srikanthonsql

  12. Pingback: SQL Grouping in the Mind’s Eye | Filter Funnel

  13. How about sp_Spaceused ‘Tablename’

    • That’s another valid option, but I don’t see it in the wild as much as the others. As with other options, this may not be 100% accurate either – you may need to run updateusage to get correct numbers – and that can have an adverse effect on large tables.

  14. number of rows limit in sql table???

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php