Re: Count record from table incorrect

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 01:37:34 -0400

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...
>
>
>>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
>>
>>
>>>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,
>>>>
>>>>
>>>.
>>>
>>>
>>>
>
>
>
>