Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Enterprise Manager Table properties vs count(*)..

Author  Topic 

reenz
Starting Member

29 Posts

Posted - 2006-04-04 : 22:12:02
When i double click on my table in enterprise manager, it shows 35367 rows. But when i do a count(*), it returns 35527! I did a commit to confirm that there's no uncommitted transaction but it's still the same.. anyone knows what should be the correct number of rows in the table? and what can i do to solve this inconsistency error?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-05 : 00:44:12
EM uses the data from sysindexes system table to get its rowcount. But if you query sysindexes before the table's statistics are updated, you will get this inconsistency. Update statistics to better ensure consistency in this regard. Using SELECT COUNT(*) will always be more accurate (but will be slower).
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-04-05 : 00:46:35
Try doing a

DBCC UPDATEUSAGE (0)

and then repeating the test.

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:25:41
It is always better to use Query Analyser than EM

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

reenz
Starting Member

29 Posts

Posted - 2006-04-05 : 01:41:20
Hmmmmm

sysindexes shows my table with 35367 instead of 35527...

I did a DBCC UPDATEUSAGE (0) and it's still showing the same results

o_O""

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:50:35
If you want to know exact count, then use select count(*) from table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -