Re: SELECT COUNT(*) returning wrong value

From: Stan Rozendal (StanRozendal_at_discussions.microsoft.com)
Date: 08/26/04 

Date: Wed, 25 Aug 2004 17:25:08 -0700

Thanks for replying Adam,

I verified the "Messages" pane and it correctly states 1 (record) select for
the 1st SELECT and 6 records for the 2nd.

I ran the DBCC CHECKDB and it reported 0 allocation errors and 0 consistency
errors.

I tried the SELECT again but still getting the same results.

I tried a variation of the original SELECT (see below) and it reports 6
records.

     select count(*)
       from op_dis_char
       where dis_id not in (select dis_id from op_dis)

Any additional ideas of what I should be checking?

"Adam Machanic" wrote:

> A) Did you check your "messages" pane after executing the second query? You
> may have warnings there (e.g. arithmetic overflow)
>
> B) Try DBCC CHECKDB ... you may have corruption.
>
>
> "Stan Rozendal" <Stan Rozendal@discussions.microsoft.com> wrote in message
> news:D13B3912-A9EF-4815-B93D-56EA49A8F3D7@microsoft.com...
> > I have a two SELECT statements that are returning different results.
> Below
> > are the two SELECT statements. The first one returns a count of 189,591
> > while the 2nd one can only find 6 to return. In addition, periodically
> the
> > first one does return a count of 6. I believe that only 6 records do
> exist
> > and am very confused why the first SELECT returns a count of 189,591.
> What
> > am I missing?
> >
> > select count(*)
> > from op_dis_char
> > where dis_id is not null
> > and dis_id not in (select dis_id from op_dis)
> >
> > select *
> > from op_dis_char
> > where dis_id is not null
> > and dis_id not in (select dis_id from op_dis)
> >
> > Below is a list of things I performed and the problem still occurring:
> > 1) dropped and recreated the indexes on both tables involved
> > 2) executed sp_spaceused [tablename] ,@updateusage='true' on both tables
> > 3) update statistics [tablename] with fullscan on both tables
> >
> > The above is done via Query Analyzer against a SQL 2000 Server.
> >
> > Thanks in advance for your help.
>
>
>

Relevant Pages

  • Re: SELECT COUNT(*) returning wrong value
    ... > Thanks for replying Adam, ... > I ran the DBCC CHECKDB and it reported 0 allocation errors and 0 ... > I tried a variation of the original SELECT and it reports 6 ...
    (microsoft.public.sqlserver.server)
  • Re: System objects problem
    ... Thanks to both of you for your advice. ... I've run DBCC CHECKDB on the databse and there are no errors: ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'siebeldb_systest1'. ... You might also have caused further damage. ...
    (comp.databases.ms-sqlserver)