Count number of records in a table

Question

Post reply

Count number of records in a table

bp

SSCarpal Tunnel

Points: 4656
More actions
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

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

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

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 1

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.

bp SSCarpal Tunnel Points: 4656 More actions · Answer 2

thanks for your quick response!

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

Billy

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

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.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 4

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

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

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

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 6

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

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

lhughes92 SSC Enthusiast Points: 146 More actions · Answer 7

lhughes92

SSC Enthusiast

Points: 146

December 4, 2002 at 12:28 pm

#443137

indid = 0 means it is a heap structure.

Lori

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 8

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

Antares686 SSC Guru Points: 125444 More actions · Answer 9

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.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 10

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 11

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.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 12

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.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 13

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 14

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.