none
Counting ROWS in a table. RRS feed

  • Question

  • I have a table with approximately 302 million rows in it.  As part of a daily health-check I run a program which displays relevant information about the table.  One of the things it reports on is how many rows/records exist in the table.  I currently use SELECT COUNT(*) FROM TLOG_ARCHIVE WHERE sale_date = '11/4/2009'  to obtain the row count however this query takes almost a full minute to execute. 

    What I've noticed is that I can right-click on the table name in the Object Explorer and choose Properties and it returns immediately and one of the values it returns is the ROW count.  Where is that value being stored at?  I've scoured through sys.objects and sys.all_objects and sysobjects but cannot find where that values is coming from.

    Does anyone know how I can get hold of that value without having to run a time consuming SELECT COUNT(*) operation?
    Thursday, November 5, 2009 3:25 PM

Answers

  • Eek!  Yep, forgot to constrain to heap or clustered index.

    select SUM(row_count) as row_count 
      from sys.dm_db_partition_stats
     where object_id = object_id('YOUR_TABLE_NAME_HERE')
       and index_id < 2

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Proposed as answer by Abdshall Thursday, November 5, 2009 6:53 PM
    • Marked as answer by Kalman TothModerator Tuesday, November 10, 2009 2:10 AM
    Thursday, November 5, 2009 3:49 PM
    Moderator

All replies

  • That values is not 100% guaranteed to be correct. Anyhow, look in sys.partitions. You can't apply any fitler to it, only work with whatever conditions you have for your partitioning (if the table is partitioned, that is).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, November 5, 2009 3:34 PM
    Moderator
  • Check out this link, it might be helpful.

    http://www.mssqltips.com/tip.asp?tip=1044
    Abdallah, PMP, ITIL, MCTS
    Thursday, November 5, 2009 3:36 PM
  • Something like this should generally be close to COUNT(*):

    select SUM(row_count) as row_count 
      from sys.dm_db_partition_stats
     where object_id = object_id('YOUR_TABLE_NAME_HERE')

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, November 5, 2009 3:37 PM
    Moderator
  • select
    t.name, max(i.rows)
    from sys.tables as t
    join sys.sysindexes as i on t.object_id = i.id
    group by t.name
    Thursday, November 5, 2009 3:40 PM
  • Something like this should generally be close to COUNT(*):

    select SUM(row_count) as row_count 
    
      from sys.dm_db_partition_stats
    
     where object_id = object_id('YOUR_TABLE_NAME_HERE')
    
    

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Phil, I ran this code on one of my tables and the number was way off count(*).

    Abdallah, PMP, ITIL, MCTS
    Thursday, November 5, 2009 3:44 PM
  • Eek!  Yep, forgot to constrain to heap or clustered index.

    select SUM(row_count) as row_count 
      from sys.dm_db_partition_stats
     where object_id = object_id('YOUR_TABLE_NAME_HERE')
       and index_id < 2

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Proposed as answer by Abdshall Thursday, November 5, 2009 6:53 PM
    • Marked as answer by Kalman TothModerator Tuesday, November 10, 2009 2:10 AM
    Thursday, November 5, 2009 3:49 PM
    Moderator
  • select
    t.name, max(i.rows)
    from sys.tables as t
    join sys.sysindexes as i on t.object_id = i.id
    group by t.name

    For 2005+ sysindexes is a compatibility view and should not be used for new development.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, November 5, 2009 3:50 PM
    Moderator
  • Thanks folks!  I kept on searching and discovered a code snippet elsewhere that I used.

    Here is the stored procedure that I ended up coding up.  It returns the same value for the number of rows in the table as does the COUNT(*) method.  The COUNT(*) method was taking over a full minute to execute, where this query seems to be almost instantaneous.

    ALTER PROCEDURE [dbo].[TL2_countAllArchRecs] 
    WITH RECOMPILE
    AS
    BEGIN
    	SELECT max(s.row_count)
    	FROM sys.objects AS o
    	INNER JOIN sys.dm_db_partition_stats AS s
    	 	ON o.object_id = s.object_id
    	WHERE o.name = 'TLOG_ARCHIVE'
    END
    I knew that there had to be a faster method of obtaining the total count of rows in the table than running a COUNT(*) operation!

    Thanks to all for pointing me in the right direction!
    Thursday, November 5, 2009 6:46 PM
  • Code Lizard,
    I'm sorry, but I have to unmark your post as the answer.  That query is incorrect - you will need to use a query similar to the one I posted.

    If a table is partitioned, your query will not return the correct count.

    Example:

    CREATE PARTITION FUNCTION myRangePF1 (int)
    AS RANGE LEFT FOR VALUES (1, 100, 1000);
    GO
    
    CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    ALL TO ([PRIMARY]);
    GO
    
    create table partition_test
    (col1 int,
     col2 int
    )
    ON myRangePS1 (col1);
    GO
    
    insert into partition_test values (1,1);
    insert into partition_test values (101,101);
    insert into partition_test values (1001,1);
    insert into partition_test values (5,2);
    go
    
    SELECT max(s.row_count) as row_count, 'MAX' as method_used
      FROM sys.objects AS o
     INNER JOIN sys.dm_db_partition_stats AS s
     	ON o.object_id = s.object_id
     WHERE o.name = 'partition_test'
     
    union all
    
    SELECT COUNT(*), 'COUNT(*)'
      FROM partition_test
      
    union all
    
    SELECT SUM(row_count), 'SUM' 
      FROM sys.dm_db_partition_stats
     WHERE object_id = object_id('partition_test');
    GO
     
    drop table partition_test;
    drop partition scheme myRangePS1;
    drop partition function myRangePF1;

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by Code Lizard Thursday, November 5, 2009 8:16 PM
    • Unmarked as answer by Code Lizard Thursday, November 5, 2009 8:16 PM
    Thursday, November 5, 2009 7:04 PM
    Moderator
  • No problem Phil.

    Your example returns the correct value as well and yours works on a partitioned table whereas mine would not.  We aren't partitioning tables here so I wasn't concerned about it, but I understand why others might have to be.

    Thanks for the sample code too!  I love it when examples work!

    Larry
    Thursday, November 5, 2009 8:16 PM
  • No problem Phil.

    Your example returns the correct value as well and yours works on a partitioned table whereas mine would not.  We aren't partitioning tables here so I wasn't concerned about it, but I understand why others might have to be.

    Thanks for the sample code too!  I love it when examples work!

    Larry

    Yep, I figured that was probably your case - not using partitioning, but to be accurate and to guarantee that it works in the future and across all tables, it is best to think ahead and write a query that accomodates future scenarios.

    Phil
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, November 5, 2009 8:23 PM
    Moderator