How to count the number of rows in a table in SQL Server

“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.

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.

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).

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

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.

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

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.


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

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

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.

Wanna learn more tricks for free?

Check out our free T-SQL Level Up online class – we guarantee it’s the best T-SQL training trailer you’ve ever seen:

Learn more and take the T-SQL course now.

Previous Post
Why Your Slow SQL Server Doesn’t Need a SQL Consultant (or Does It?)
Next Post
San Diego Class Recap

40 Comments. Leave new

  • 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.

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

  • 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);

    • Jes Schultz Borland
      February 18, 2014 3:20 pm

      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.

    SELECT OBJECT_NAME(id), rows FROM sysindexes WHERE indid < 2

    This used to work on sql2000 too.

    • Jes Schultz Borland
      February 20, 2014 8:13 am

      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.

  • 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.

  • Why is sys.indexes needed in this?

    DECLARE @TableName sysname
    SET @TableName = ‘bigTransactionHistory’

    SELECT TBL.object_id,, 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 = @TableName
    AND IDX.index_id < 2
    GROUP BY TBL.object_id,;

  • 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.

  • Nakul Vachhrajani
    February 23, 2014 1:34 am


    Years ago, I wrote this piece on the alternatives to SELECT COUNT(*) [] – 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!

  • 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.

  • How about sp_Spaceused ‘Tablename’

    • Jes Schultz Borland
      May 23, 2014 8:31 am

      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.

  • nishant shah
    June 25, 2014 11:48 pm

    number of rows limit in sql table???

  • Jes, as always great article! Just wanted to add a note regarding the use of SYS.DM_DB_PARTITION_STATS. It is only valid for information for the current database context and it cannot be used to reference another database. So if you were say, comparing counts between tables (like in a publisher/subscriber scenario) I don’t believe you could use this DMV…or could you?

  • Bryan Hamilton
    March 18, 2015 5:41 pm

    How about powershell? in sqlps : using one line as below….

    e.g. :

    PS SQLSERVER:\SQL\\DEFAULT\Databases\\Tables> dir | select name, rowcount

  • Thanks!

    Is there any possibility to get the row count based on table column values as parameter.

    SELECT *
    FROM bigTransactionHistory
    where column1 = ‘ ‘

    Joing with your query :

    DECLARE @TableName sysname
    SET @TableName = ‘bigTransactionHistory’

    SELECT TBL.object_id,, 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
    INNER JOIN bigTransactionHistory
    AND PART.index_id = IDX.index_id
    WHERE = @TableName
    AND IDX.index_id < 2
    GROUP BY TBL.object_id,

  • Is there any way to apply SYS.DM_DB_PARTITION_STATS on a SQLSERVER View. I have to Count Records from a table based on multiple inner joins. I have half a million records and my Count(ID) Query takes 20 seconds.

    • Xaveed – generally, you don’t want to join to system tables in end user queries. You can end up with serial (as opposed to parallel) queries, and some ugly locking issues.

  • The query on sys.partitions can be made simpler, so that it only hits 1 table (same as query on sys.dm_db_partition_stats):
    SELECT SUM(p.rows) AS rows
    FROM sys.partitions p
    WHERE p.object_id = OBJECT_ID(‘MyTable’)
    AND p.index_id IN (0,1); — heap or clustered index

    Apparently sp_spaceused uses sys.dm_db_partition_stats.

    sys.partitions is available to public role, whereas sys.dm_db_partition_stats requires VIEW DATABASE STATE permission.

    Would be interesting to see a more detailed comparison of the two views.

  • […] to add some non-trivial extra load to that process (or the servers doing said processing).  So let’s avoid COUNT(*) shall we?  Unfortunately, the top Google results don’t readily point to this, but […]

  • You have used count(*) in both of the queries. One should be count(1)

  • exec sp_spaceused

  • Update for Memory_Optimized tables, which have no clustered index, and whose heap index is not tracked in partition_stats:

    SELECT top 1 ps.row_count
    FROM sys.indexes as i
    sys.dm_db_partition_stats as ps
    ON ps.object_id = i.object_id
    and ps.index_id = i.index_id
    WHERE i.object_id = OBJECT_ID(‘dbo.[MyTable]’)
    i.[type] — sort by heap/clust idx 1st
    , i.is_primary_key desc
    , i.is_unique desc

  • Can you please make some example get the row count based on table column values as parameter with Hussain question???

  • This will get (non-zero) rows counts for tables that contain a specific column name.

    SELECT OBJECT_NAME(a.object_id), SUM(row_count) AS rows
    FROM sys.dm_db_partition_stats a
    inner join sys.columns b
    on a.object_id = b.object_id
    where = ’employid’
    and a.object_id = b.OBJECT_ID
    AND index_id 0

    • Somehow in my previous reply the full query string got truncated. Here it is (looking for tables with data containing the column EMPLOYID):

      SELECT OBJECT_NAME(a.object_id), SUM(row_count) AS rows
      FROM sys.dm_db_partition_stats a
      inner join sys.columns b
      on a.object_id = b.object_id
      where = ’employid’
      and a.object_id = b.OBJECT_ID
      AND index_id 0

  • Oh boy!!! It looks like the GT and LT symbols drop code. Here’s the code with those symbols replaced by GT and LT. (Sorry for the multiple posts – moderator feel free to delete previous code-defective comments.)

    SELECT OBJECT_NAME(a.object_id), SUM(row_count) AS rows
    FROM sys.dm_db_partition_stats a
    INNER JOIN sys.columns b
    ON a.object_id = b.object_id
    WHERE = ’employid’
    AND a.object_id = b.OBJECT_ID
    AND index_id LT 2
    GROUP BY OBJECT_NAME(a.object_id)
    HAVING SUM(row_count) GT 0

  • HI, I need a sample for the below requirement. If the count(customerid) >1 means, for 1st row in count, i need to print ‘M’, and for the second record i need to print ‘N’ and so on. Its tought to query and to get logic. Anybody can help in this?

  • Action type wise count which are Done on 9/19.