Count record from table incorrect

When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,
Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,
|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,

>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the[vbcol=seagreen]
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all
>
>.
>
|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> successful execution of the
> records.
> all
|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
"Aaron [SQL Server MVP]" wrote:

> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>