Post reply

Alternative way to get the tables row count

  • December 14, 2004 at 9:21 am

    #60032

    I have read some IT publications were journalists say that there could be an alternative and effective way to count a table’s    rows; rather than use SELECT COUNT(*) FROM MYTABLE …………[1] is 

    SELECT rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2  …… [2]
    However, unfortunately, some times it does not work fine for me.
    I used DTS in Vb6.0  to load Data Form Text File to SQL SERVER 2000 DB.
    After that if I used the method [2] or right click on the table from EnterPrise Manager then click on Properties then it shows rows as 1841977
    Whereas the query [1] produced the result  as  1842251.
     
    Can any one help me to get rid from this confusion?
     
    Thanks in advance
    Muntasir 
      



    ..Better Than Before...

  • December 14, 2004 at 9:39 am

    #533793

    Update the statistics on the table. This can get out of synch. Also, you should have a clustered index on the table (indid =1), which ensures accuracy most of the time.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 14, 2004 at 9:46 am

    #533798

    sysindexes is used strictly for a close enough row count for the indexes themselves.  It was not meant to be queried off of for row count.  You could possibly add an IDENTITY field and then do MAX on that but again if rows are DELETED this woulod be off as well....

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • December 14, 2004 at 10:08 am

    #533803

    It takes time for SQL Server to update the usage.  You can kick off the process yourself with DBCC UPDATEUSAGE.

  • December 14, 2004 at 1:26 pm

    #533840

    Try this.

    declare @rc int

    EXEC sp_table_validation 'tablename', @rc OUTPUT, @rowcount_only=1

    select @rc

  • December 15, 2004 at 10:07 am

    #533989

    What about sp_spaceused?

     

  • December 15, 2004 at 10:43 pm

    #534078

    sp_spaceused uses the numbers from sysindexes, so it will be just as inaccurate. sysindexes can be seriously inaccurate. Ever see databases with negative free space? I just had a problem today where DBCC UPDATEUSAGE dropped the rowcount on a table from over 1 million to 127. It was performing abysmally (before UPDATEUSAGE) because it was choosing bad execution plans based on the bad count.

    One good use I've found for sysindexes is for checking the progress of a large insert. Even if the destination table is locked exclusively, "SELECT reserved / 128.0 AS MegaBytes WHERE ID = OBJECT_ID('MyTable') AND indid < 2" will show how much data is in the table. The rowcnt field will not be updated until the insert completes, but reserved is updated continuously. As long as the MegaBytes value is increasing you know your remote query hasn't gotten hung up.

  • December 19, 2004 at 8:49 am

    #534409

    Hi,

        It is working like as below: 

    dbcc updateusage ('Mydatabase','MYTABLE')   WITH   COUNT_ROWS

    GO

    select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

    …[2]

     

    But it takes longer time than select count(*) from MYTABLE …[1]

    For instance;

                     My table has 15338371 rows

                    And [1] takes 30 seconds, Where as [2] takes 38 seconds on average.

    So is there any way to check whether sysindexes is updated or not, then I may use the logic

     

    If UPDATED THEN

             select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

    Else

        dbcc updateusage ('Mydatabase','MYTABLE')

    WITH     COUNT_ROWS

    GO

    select rows FROM sysindexes WHERE id = OBJECT_ID('MYTABLE') AND indid < 2

     

     



    ..Better Than Before...

  • December 20, 2004 at 5:49 am

    #534442

    As others have said, sysindexes is for approximation only, it is not guaranteed to show the exact number of rows at any given time.

    If your process requires that you find an exact count you can trust at all times, then the only way to get that is to do what you started with -

    'select count(*) from myTable'.

    /Kenneth

  • December 21, 2004 at 11:09 am

    #534649

    ok

    Thanks to u ALL



    ..Better Than Before...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply