Mismatch between Count(*) and Properties -> Rows

I have a client who has reported a discrepancy in their database. In
their test database a certain report returns 5,333 rows, but in the
production database it returns 5, 332 rows.

I'll get to the bottom of it in due course, but I came across an
oddity. I wanted to know how many rows there were in a particular
table on both databases so I wrote:

SELECT COUNT(*) AS NumRows FROM Answer

It returned 1,919,456. However, if I click on the Answer table in
Enterprise Manager and select Properties it tells me that there are
1,919,421 rows. I've tried doing a refresh but it didn't work.

The thing is that the database is pretty static - in fact there's been
no activity (apart from me examining it) for a couple of days.

Thoughts?

Edward

Re: Mismatch between Count(*) and Properties -> Rows

On Fri, 29 Jun 2007 07:28:03 -0700, teddysnips@hotmail.com wrote:

>SELECT COUNT(*) AS NumRows FROM Answer
>
>It returned 1,919,456. However, if I click on the Answer table in
>Enterprise Manager and select Properties it tells me that there are
>1,919,421 rows. I've tried doing a refresh but it didn't work.

Those statistics are not always accurate. To fix them run

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

Roy Harvey
Beacon Falls, CT

Re: Mismatch between Count(*) and Properties -> Rows

Roy Harvey wrote:

> On Fri, 29 Jun 2007 07:28:03 -0700, teddysnips@hotmail.com wrote:
>
>> SELECT COUNT(*) AS NumRows FROM Answer
>>
>> It returned 1,919,456. However, if I click on the Answer table in
>> Enterprise Manager and select Properties it tells me that there are
>> 1,919,421 rows. I've tried doing a refresh but it didn't work.
>
> Those statistics are not always accurate. To fix them run
>
> DBCC UPDATEUSAGE(0) WITH COUNT_ROWS

Is it COUNT(*) or EM - Properties that is sometimes inaccurate? Under
what circumstances may it be inaccurate?

Re: Mismatch between Count(*) and Properties -> Rows

On Fri, 29 Jun 2007 18:53:15 -0700, Ed Murphy
wrote:

>Roy Harvey wrote:
>
>> On Fri, 29 Jun 2007 07:28:03 -0700, teddysnips@hotmail.com wrote:
>>
>>> SELECT COUNT(*) AS NumRows FROM Answer
>>>
>>> It returned 1,919,456. However, if I click on the Answer table in
>>> Enterprise Manager and select Properties it tells me that there are
>>> 1,919,421 rows. I've tried doing a refresh but it didn't work.
>>
>> Those statistics are not always accurate. To fix them run
>>
>> DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
>
>Is it COUNT(*) or EM - Properties that is sometimes inaccurate? Under
>what circumstances may it be inaccurate?

Enterprise Manager. EM gets the count from index information
maintained by the system. The overhead of keeping the numbers
absolutely accurate at all times would be prohibitive, so the row
count gets out of sync with reality at times. The same goes for space
allocation, which UPDATEUSAGE also fixes.

SELECT COUNT(*) will always be correct.

Roy Harvey
Beacon Falls, CT