Record Counts not matching

Question

Record Counts not matching

Juanita

SSCertifiable

Points: 5300
More actions
October 25, 2005 at 11:20 am

#102884

HI. When I go into Enterprise manager and double click on a table and view the table properties, I see the number of rows in that table. Ex. table1 has 3917 rows. NOW, when i do a select on all records OR a select Count(*) on this same table1 i get a count of 9300 rows. Can someone explain how this can be? Very confused.
Juanita

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kory Becker Hall of Fame Points: 3585 More actions · Answer 1

Do not trust any rowcount other than a Select Count(*) From table as most are based on the table statistics for performance of reporting. If your statistics are stale well you are seeing the rest!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

you can run sp_updatestats and then dbcc updateusage (not sure of the 2nd one)

Juanita SSCertifiable Points: 5300 More actions · Answer 3

Thanks! i always thought the row count on the table properties were accurate.

So if I run sp_updatestats, would that show an accurate row count on the table properties?

Thanks,

Juanita

Kory Becker Hall of Fame Points: 3585 More actions · Answer 4

What are you trying to accomplish? Depending on the type of activity on a table the statistics can become stale in seconds. The ONLY accurate wat to get a rowcount is Select Count(*) From dbo.table!

Juanita SSCertifiable Points: 5300 More actions · Answer 5

I wasn't trying to accomplish anything in particular. I just happen to view the rowcount on a table and then I did a select and saw the difference in rows. That's when i started to question what was happening. But you all just opened my eyes to a little bit more about sql server. Now i know the best way to get a row count of a table.

Thanks to all !!

Juanita

Alexander Yuryshev Old Hand Points: 390 More actions · Answer 6

When you click on table you see rough result:

EM do this so:

SELECT

SO.name

,SI.rowcnt

FROM

sysobjects SO

JOIN sysindexes SI ON SO.id = SI.id AND SI.indid <2

WHERE

SO.xtype = 'U'

It's good for administration purposes, not for buisness logic programming.

And think about such thing:

There is no such thing as COUNT(*) without identifiing lock-level.

SELECT COUNT(*) FROM Table(NOLOCK)

SELECT COUNT(*) FROM Table(READPAST)

SELECT COUNT(*) FROM Table(READCOMMITTED)

Results may vary.

Cheers!

Mark Hickin Say Hey Kid Points: 693 More actions · Answer 7

Agree with Alexander's post on this, although I would like to point something out from an earlier post that was not quite right.

SQL Server keeps the row counts updated in a fairly lazy way. It can get quite out of step with reality, esp on large tables. If for some reason you need to fix that (count(*) is still the only way to know for sure) have a look at this:

dbcc updateusage (db_name, tablename, index_id) WITH COUNT_ROWS

sp_updatestats will update the stats blob, and will not, as far as I can see, update this rowcnt field on sysindexes.

Mark