Record count after Insert

17 Aug 2006 6:02 PM

gv

Hi all,

I have added 70 records from another table into a table.
When Selecting the count from Query Analyzer I get
the correct new count. When right clicking on properties of the table
I get the old count.

Can someone explain this why?

thanks
gv

17 Aug 2006 6:17 PM

Adi

The EM doesn't run select count statement on the table. Instead it
gets the number of records from the column rowcnt in sysindexes. The
problem is that this column is not accurate (as you found out). I
don't know exactly when and how it gets updated with the real number,
but you can run DBCC UPDATEUSAGE that will update the column with the
correct number (but in time the number might not be accurate again).

Adi
gv wrote:
Show quote

> Hi all,
>
> I have added 70 records from another table into a table.
> When Selecting the count from Query Analyzer I get
> the correct new count. When right clicking on properties of the table
> I get the old count.
>
> Can someone explain this why?
>
> thanks
> gv

17 Aug 2006 6:22 PM

Aaron Bertrand [SQL Server MVP]

> When right clicking on properties of the table I get the old count.

This is not an accurate way of using a query to get the data. Enterprise
Manager caches a lot of this data and gets it from sysindexes, which is not
updated constantly. If every time you right-clicked anywhere in EM it went
behind the scenes and did a current count from all the tables, everyone
would complain that EM is too slow becuase it would hang there for a
LOOOOONG time, at least on many of the systems I work on, where tables with
millions of rows are very common.

If you want an accurate count, use SELECT COUNT(*). If you just want a
ballpark you can add WITH (NOLOCK). But stop pointing and clicking in
Enterprise Manager... it will get you nowhere fast.

17 Aug 2006 6:45 PM

gv

Thanks everyone!!!

I always use Select count!!!! I was just trying to understand why it was
reporting wrong counts.

thanks
Again

gv

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:u0Jv0niwGHA.3964@TK2MSFTNGP04.phx.gbl...
>> When right clicking on properties of the table I get the old count.
>
> This is not an accurate way of using a query to get the data. Enterprise
> Manager caches a lot of this data and gets it from sysindexes, which is
> not updated constantly. If every time you right-clicked anywhere in EM it
> went behind the scenes and did a current count from all the tables,
> everyone would complain that EM is too slow becuase it would hang there
> for a LOOOOONG time, at least on many of the systems I work on, where
> tables with millions of rows are very common.
>
> If you want an accurate count, use SELECT COUNT(*). If you just want a
> ballpark you can add WITH (NOLOCK). But stop pointing and clicking in
> Enterprise Manager... it will get you nowhere fast.
>

17 Aug 2006 7:36 PM

Hari Prasad

Hi,

Can you update the statistics and see.

Update Statistics <tableName>

Thanks
Hari
SQL Server MVP

Show quote

"gv" wrote:

> Hi all,
>
> I have added 70 records from another table into a table.
> When Selecting the count from Query Analyzer I get
> the correct new count. When right clicking on properties of the table
> I get the old count.
>
> Can someone explain this why?
>
> thanks
> gv
>
>
>

17 Aug 2006 7:37 PM

Alejandro Mesa

gv,

> When right clicking on properties of the table I get the old count.

I guess you are talking about "Enterprise Manager". If so, EM brings the
info from the system table [sysindexes] and sometime this value get out of
sync. Execute "dbcc updateusage" or "sp_spaceused 'table_name', 'true'", from
QA, to correct the inaccuracies.

AMB

Show quote

"gv" wrote:

> Hi all,
>
> I have added 70 records from another table into a table.
> When Selecting the count from Query Analyzer I get
> the correct new count. When right clicking on properties of the table
> I get the old count.
>
> Can someone explain this why?
>
> thanks
> gv
>
>
>