Why # of Rows Not = # of Records???

[HenryE]

Help!!!

I have a table in a SQL Server 2000 database where the number of rows (from Table Properties) is different from the number of records in the table. The difference is small, 12,748 rows, versus 12,750 records, but why is it happening? Especially since just a few hours ago, table properties was showing 12,750 rows and nobody has used the table since.

Anybody have any idea?

Thanks.

Henry

 

[hneal98]

Maybe duplicates?

 

[HenryE]

Thanks for the suggestion. I checked, but there are no duplicates.

Henry

 

[r937]

rows = records, they aint no difference

dunno about table properties, but

select count(*) from thetable

will always give the correct answer


rudy

 

[Transcend]

How are you determining the number of records?

Hopefully you don't have an autonumber ID and you are checking the highest one - because this would not be the number of records in your table.

Transcend

 

[Glyndwr]

Henry,
you say you are comparing number of rows with rowcount in the properties form of the table.

The properties form uses the value found in the sysinexes table. this can NOT be guaranteed to be up to date.

if you wish to use this value then you must ensure the sysindexes table is updated

the following command should do it

DBCC UPDATEUSAGE ('yourDb','yourTable') WITH COUNT_ROWS




Glyndwr

 

[LFCfan]

I've noticed this before when looking at the number of rows in EM versus SELECT COUNT(*). again, only a couple of rows difference. it didnt seem to follow any particular pattern though, and i've only seen it once on a couple of tables.

perhaps EM gets its count from sysindexes? which might not be accurate depending on whether statistics have been recently updated? just guessing here though

as r937 says, SELECT COUNT(*) will always be accurate.

 

[HenryE]

Thanks Everyone.

The problem has gone away by itself, but I learned a lot from everyone's suggestions.

Thanks.

Henry

 

[nigelrivett]

The number of rows is retrieved from rows in sysindexes

select rows from sysindexes where id = object_id('tblname') and indid in (0,1)

This value is not always updated when the number of rows changes but may be corrected by dbcc updateusage.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.