Post reply

counting rows in a table

  • December 28, 2006 at 3:58 pm

    #176223

    I have a table A with a non-clustered index on it.

    when I use "select rows from sysindexes where id=object_id('table A') and indid<2" it got a number for row count. Then I used "select count(*) from table A (nolock)" and I got a different number (a few hundred less).

    Is it supposed to do that? Has anybody got such different results.

  • December 28, 2006 at 4:15 pm

    #680395

    Sysindexes contains an approximate row count and is accurate right after statistics are updated.  If you need to know EXACTLY how many rows are in a table count() is the answer.

    And then again, I might be wrong ...
    David Webb

  • December 28, 2006 at 5:03 pm

    #680408

    yep you need to rebuild indexs and recompute stats if you want to use the no in sysindexes. Whats wrong with using count?

    www.sql-library.com[/url]

  • December 28, 2006 at 5:35 pm

    #680414

    even after updating statistics the numbers are not matching. The table has 75 columns and row size is 842 bytes. there are around 1,000,000 rows in the table. I have no problem using count(*) to get row count. I am just curious as to why the other way is not working for me.

  • December 28, 2006 at 5:47 pm

    #680418

    Stab in the dark here :

    Have you tried it with this column instead : rowcnt

  • December 28, 2006 at 5:50 pm

    #680420

    both fields have the same number.

  • December 28, 2006 at 5:55 pm

    #680423

    How about this if you're not on a production server :

    sp_updatestats

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    Rerun both queries.

  • December 28, 2006 at 6:15 pm

    #680424

    i dont have permission to execute dropcleanbuffers or freeproccache

  • December 28, 2006 at 6:39 pm

    #680428

    Well I'm out of ideas then...

    BTW, why can't you run that command on a dev server??

  • December 28, 2006 at 6:49 pm

    #680432

    my login does not have permission to run those commands on the server. I am not a member of the sysadmin role. thanks for the help. in the future, i will use count(*) rather than sysindexes just to be safe.

  • December 29, 2006 at 8:49 am

    #680518

    When you are using the NOLOCK hint, you will often see consistency errors. Using NOLOCK you can, under some circumstances, read the same row multiple times, miss rows completly etc. When you use NOLOCK, in effect you are telling SQL Server that you aren't that concerned with the accuracy of the results.

    This may not be the problem, but it's worth keeping in mind.

    SQL guy and Houston Magician

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

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