Rowcount reporting incorrectly...

Why when I double click my table, does the "Rows"
field show 10 records, but when I do a SELECT COUNT(id)
it show me the right amount?
SQL 2000 Enterprise, Windows 2003
I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
INDEXDEFRAG...etc.
I've never heard of this before.EM caches alot of information. Did you try refreshing the data? Exit
and restart EM and see if the problem still appears.|||DBCC UPDATEUSAGE may improve the accuracy of this number, but it would have
to be executed each time you want to look at the number. Read up on
statistics; how they are used and when they are updated.
"isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
> Why when I double click my table, does the "Rows"
> field show 10 records, but when I do a SELECT COUNT(id)
> it show me the right amount?
> SQL 2000 Enterprise, Windows 2003
> I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
> INDEXDEFRAG...etc.
> I've never heard of this before.
>|||... and it has to be executed using the COUNT_ROWS option (which mean it wi
ll take a longer
time...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <someone@.microsoft.com> wrote in message news:e6vBNy4RGHA.5036@.TK2MSFTNGP12.phx.gbl...

> DBCC UPDATEUSAGE may improve the accuracy of this number, but it would hav
e to be executed each
> time you want to look at the number. Read up on statistics; how they are u
sed and when they are
> updated.
>
> "isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
> news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql

Row count mismatch

Hello!
When I double click on a table in EM I get 536929 rows. When I do a select
count(*) in the QA, I get 537054 rows. A difference of 125. Why do I get
two different row counts for the same table from two different sources?
Baffled,
SeanEM may pull the record count from sysindexes table or some other meta-source
;
I'm pretty sure it doesn't do a COUNT(*) of the actual table.
"sean sobey" wrote:

> Hello!
> When I double click on a table in EM I get 536929 rows. When I do a selec
t
> count(*) in the QA, I get 537054 rows. A difference of 125. Why do I ge
t
> two different row counts for the same table from two different sources?
> Baffled,
> Sean
>
>|||sean sobey wrote:
> Hello!
> When I double click on a table in EM I get 536929 rows. When I do a
> select count(*) in the QA, I get 537054 rows. A difference of 125.
> Why do I get two different row counts for the same table from two
> different sources? Baffled,
> Sean
SQL Server does not track row counts. You can usually get a pretty good
estiamte of rowcount from sysindexes (indid 0 or 1), but if the index
statistics are out of date, the number could be off. I'm guessing that
SQL EM does not perform a COUNT(*) behind the scenes since it might be a
performance issue on large tables.
Of course, now that I'm readind your post in more detail (coffee just
kicked in), I think what you're saying is that you opened a table in SQL
EM (really bad idea in most cases - but that's another story) and the
rows as reported in the row count column differ from row count you see
in QA with a COUNT(*). Could either be that SQL EM is using sysindexes
to show the estimate (it can't know the total row count until all rows
are pulled back).
Trust the QA results and stay away from using SQL EM for opening tables.
David Gugick
Imceda Software
www.imceda.com|||Hi Sean,
Enterprise manager uses the system stored rpocedure "sp_MStablespace" to get
the rows in a table. Actually this stored procedure reads the rowcnt column
from sysindexes table. If the value is incorrect you could run the command
DBCC UPDATEUSAGE to correct the inconsistency in the sysindexes table.
Usage:-
--
DBCC UPDATEUSAGE ('DBNAME','Tablename')
GO
After that you could see the number of rows returned by enterprise manager
and select Count(*) be same.
Thanks
Hari
SQL Server MVP
"sean sobey" wrote:

> Hello!
> When I double click on a table in EM I get 536929 rows. When I do a selec
t
> count(*) in the QA, I get 537054 rows. A difference of 125. Why do I ge
t
> two different row counts for the same table from two different sources?
> Baffled,
> Sean
>
>|||Thank you everyone for clearing this up.
Sean
"Hari Pra" <HariPra@.discussions.microsoft.com> wrote in message
news:916A584E-4110-4F8C-8073-3082C2F52DF1@.microsoft.com...
> Hi Sean,
> Enterprise manager uses the system stored rpocedure "sp_MStablespace" to
get
> the rows in a table. Actually this stored procedure reads the rowcnt
column
> from sysindexes table. If the value is incorrect you could run the command
> DBCC UPDATEUSAGE to correct the inconsistency in the sysindexes table.
>
> Usage:-
> --
> DBCC UPDATEUSAGE ('DBNAME','Tablename')
> GO
> After that you could see the number of rows returned by enterprise manager
> and select Count(*) be same.
> Thanks
> Hari
> SQL Server MVP
> "sean sobey" wrote:
>
select
get

row count

I opened a database in Entreprise Manager, selected a table, double clicked
on it, and the property window poped up. I read the Rows of the table. Then,
I righ clicked the table->Open Table->Query, and run a query to count the r
ows (based on the primary k
ey column). It came up with a different number. I'm sure the table wasn't up
dated during the process. So which number is the right number? Thanks.
Ed.select count(*) gives the correct number.
the number shown in enterprise manager is not always current.
read up on sp_spaceused and dbcc updateusage in bol.
Ed wrote:

quote:

---

> I opened a database in Entreprise Manager, selected a table, double clicked on it, and the
property window poped up. I read the Rows of the table. Then, I righ clicked the table->Ope
n Table->Query, and run a query to count the rows (based on the primary

---

key column). It came up with a different number. I'm sure the table wasn't updated during the proc
ess. So which number is the right number? Thanks.

quote:

---

> Ed.

---