Post reply

Count number of records in a table

  • December 4, 2002 at 11:12 am

    #64283

    What is the best way to count the number of records in a table?

    Is it:

    a) select count(*) from table1

    b) look at the indexes in the table (if there is any)

    c) other?

    Also, in EM, when I right-click properties a table, it tells me the number of records in the table. Does anyone know how this number is generated?

    Thanks in advance

    Billy

  • December 4, 2002 at 11:29 am

    #443122

    declare @table sysname

    DECLARE @Row_Count int

    SELECT @Row_Count = rows FROM sysindexes

    WHERE id = OBJECT_ID(@table) AND indid < 2

    does your B) answer.

  • December 4, 2002 at 11:37 am

    #443125

    thanks for your quick response!

    however, how come you use "rows" and not "rowcnt"?

    Billy

  • December 4, 2002 at 11:37 am

    #443124

    Also, in EM, when I right-click properties a table, it tells me the number of records in the table. Does anyone know how this number is generated?

    That number comes from sysindexes.

  • December 4, 2002 at 11:42 am

    #443126

    Rows and Rowcnt hold the same definition. Rows was added for backwards compatibility.

  • December 4, 2002 at 11:51 am

    #443129

    Thanks!

    does anyone know what kind of an index it is when indid=0 in the sysindex table?

    I checked BOL and there was nothing. I know when indid=1, it is a clustered index.

    thanks in advance,

    Billy

  • December 4, 2002 at 12:20 pm

    #443134

    quote:

    how come you use "rows" and not "rowcnt"?

    I did it from memory. I think rowcnt is the current column name.

  • December 4, 2002 at 12:28 pm

    #443137

    indid = 0 means it is a heap structure.

    Lori

  • December 4, 2002 at 1:11 pm

    #443149

    IndexID of 0 means no index, heap structure from table

    IndexId of 1 is the clustered index

    IndexId of 2-254 are nonclustered indexes

    IndexId of 255 is entry for text or image values in table

  • December 4, 2002 at 2:48 pm

    #443189

    I do not trust sysindexes row value. In SQL 7 this value may not be right at any given time. There is a KB Article but I cannot find it right off. May be the same issue in 2000. I will have to find or if anyone else knows about please post here.

  • December 4, 2002 at 3:06 pm

    #443195

    quote:

    I do not trust sysindexes row value.

    The value in sysindexes comes from the "update statistics" process and will more than likely be inaccurate if you have disable "auto update stats" in your DB. For large tables (100's of millions of rows) it is often faster to use this query to get a general idea (after all, when you have that many, what's 1000 more or less?) than it is to actually count the rows. If you need an accurate row count, you must use select count(*)

    You can speed this up by having a clustered index on the table.

  • December 4, 2002 at 3:17 pm

    #443198

    Thanks Don, didn't think I was nuts. And since it was the statistics thing I was thinking of even with Auto Update Stats on it may be in acurate and percentage of change must take place first before Auto Update takes place. ALso, periodically I have seen doing a Truncate leave the value what it was before.

  • December 4, 2002 at 3:21 pm

    #443201

    quote:

    ALso, periodically I have seen doing a Truncate leave the value what it was before.

    I thought Truncate cleaned everything up and made it good as new. Maybe it just doesn't bother to update this since it's not documented and is in theory "internal use only" info.

  • December 4, 2002 at 3:45 pm

    #443207

    Antares686, your correct in that its not always accurate. However, (gotta hate that however) that's where rowmodcntr comes in. This value shows the changes between updates of statistics and re-indexing of the tables, at which time it goes back to 0 and the rows are accurate.

  • December 4, 2002 at 4:41 pm

    #443218

    quote:

    Antares686, your correct in that its not always accurate. However, (gotta hate that however) that's where rowmodcntr comes in. This value shows the changes between updates of statistics and re-indexing of the tables, at which time it goes back to 0 and the rows are accurate.

    Thanks for the extra input. I was not aware of that or forgot (1 or the other). I will look at myself but makes sense to have something like that. But does the rowmodcntr actually help. Wouldn't it record a 1 for each modification UPDATE, INSERT, and DELETE and thus could not combine the 2 to get an accurate count? If so then SELECT COUNT(*) is still going to be more accurate.

Viewing 15 posts - 1 through 15 (of 23 total)

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