Table Count Issue

[dbomrrsm]

Can anyone explain what is happening here ?

I have created a table and inserted 200 rows as follows:

create table counter_test (counter int)
declare @counter int
set @counter = 0
WHILE @counter < 200
BEGIN
INSERT INTO counter_test (counter)
VALUES(@counter+1)
SET @counter = @counter + 1
END

Then go to Enterprise Manager (EM) and click on the table and select properties and it tells me there are 200 rows in the table - fine.

Then run:

select o.name, rows
from sysobjects o
 inner join sysindexes i 
on o.id = i.id
where i.indid < 2 
and xtype='u' 
and o.name not in ('dtproperties')
and o.name = 'counter_test'
order by o.name

And that tells me there are 200 rows in the table.

So here is the problem -

Run

drop table counter_test

Then rerun the create and populate before but change to insert 5000 rows i.e.

create table counter_test (counter int)
declare @counter int
set @counter = 0
WHILE @counter < 5000
BEGIN
INSERT INTO counter_test (counter)
VALUES(@counter+1)
SET @counter = @counter + 1
END

Then when you look in EM there are not 5000 rows also running:

select o.name, rows
from sysobjects o
 inner join sysindexes i 
on o.id = i.id
where i.indid < 2 
and xtype='u' 
and o.name not in ('dtproperties')
and o.name = 'counter_test'
order by o.name

shows the same amount of rows as EM but:

select count(*)
from counter_test

in QA shows the correct number of rows of 5000.

Can anyone explain this ?


[blue]DBomrrsm[/blue]

 

[ks2000]

Hi,

I checked this As you mentioned above.
Its showing correct count in EM.


Good Luck
Gopala Krishna Kakani

 

[vongrunt]

Hm... I always got 5000 even with EM grid constantly open.

EM calls sp_MStablespace and uses sysindexes.rowcnt. You are using sysindexes.rows. Shouldn't matter, though "rows" is left for backward compatibility. Probably some kind of deferred/delayed write.