We're currently getting the following behaviour on a SQL2K production
database at the moment:

select count(*) from Tab_1
-------------
3804049

(1 row(s) affected)

select count(*) from Tab_1 where datearchived is null
-----------
3753144

(1 row(s) affected)

select count(*) from Tab_1 where (batch is not null or batch != ') and
datearchived is null
-----------
3774095

(1 row(s) affected)

In other words, adding a further restriction returns MORE records.

To make it scarier, this is NOT consistent; sometimes we get the above
behaviour, and then for a few minutes the second query will return
fewer records, before reverting back to the same incorrect results
again:

-----------
3804049

(1 row(s) affected)

-----------
3753145

(1 row(s) affected)

-----------
3751486

(1 row(s) affected)

The incorrect results are the norm, though.

I've dropped / recreated all indexes in case one of them was corrupt -
made no difference.
We've DTS'd the table to another server, and failed to reproduce the
problem.
We've BCP'd the data to another server and failed to reproduce the
problem.
We're currently recreating a copy database by attaching a copy of the
data file taken last night from the production db, in the hope that a
binary copy will enable us to reproduce it outside the production
server. If we can do that, then we can play around with the data a bit
more.

We're 99% sure the problem lies in the NULL values being held in the
datearchived column, but we aren't sure exactly what is wrong with
them.

Has anyone seen anything remotely like this before? Anyone got any
further ideas we could borrow?

Phil,

query 3 is an invalid query. It tries to compare column batch to an
unclosed string. As it is written now, this unclosed string includes the
text "and datearchived is null".

Gert-Jan


Phil wrote:
>
> We're currently getting the following behaviour on a SQL2K production
> database at the moment:
>
> select count(*) from Tab_1
> -------------
> 3804049
>
> (1 row(s) affected)
>
> select count(*) from Tab_1 where datearchived is null
> -----------
> 3753144
>
> (1 row(s) affected)
>
> select count(*) from Tab_1 where (batch is not null or batch != ') and
> datearchived is null
> -----------
> 3774095
>
> (1 row(s) affected)
>
> In other words, adding a further restriction returns MORE records.
>
> To make it scarier, this is NOT consistent; sometimes we get the above
> behaviour, and then for a few minutes the second query will return
> fewer records, before reverting back to the same incorrect results
> again:
>
> -----------
> 3804049
>
> (1 row(s) affected)
>
> -----------
> 3753145
>
> (1 row(s) affected)
>
> -----------
> 3751486
>
> (1 row(s) affected)
>
> The incorrect results are the norm, though.
>
> I've dropped / recreated all indexes in case one of them was corrupt -
> made no difference.
> We've DTS'd the table to another server, and failed to reproduce the
> problem.
> We've BCP'd the data to another server and failed to reproduce the
> problem.
> We're currently recreating a copy database by attaching a copy of the
> data file taken last night from the production db, in the hope that a
> binary copy will enable us to reproduce it outside the production
> server. If we can do that, then we can play around with the data a bit
> more.
>
> We're 99% sure the problem lies in the NULL values being held in the
> datearchived column, but we aren't sure exactly what is wrong with
> them.
>
> Has anyone seen anything remotely like this before? Anyone got any
> further ideas we could borrow?

You're right. Not sure how that happened, but it is just a cut and
paste error. Please assume that this is an empty string '' -
everything else is the same.

Check out MSKB 814509
<http://support.microsoft.com/default.aspx?scid=kb;en-us;814509> to see if
it applies. If so, another workaround is to specify a MAXDOP 1 hint.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Phil" <philipyale@btopenworld.com> wrote in message
news:1105438626.842951.31340@f14g2000cwb.googlegro ups.com...
> We're currently getting the following behaviour on a SQL2K production
> database at the moment:
>
> select count(*) from Tab_1
> -------------
> 3804049
>
> (1 row(s) affected)
>
> select count(*) from Tab_1 where datearchived is null
> -----------
> 3753144
>
> (1 row(s) affected)
>
> select count(*) from Tab_1 where (batch is not null or batch != ') and
> datearchived is null
> -----------
> 3774095
>
> (1 row(s) affected)
>
> In other words, adding a further restriction returns MORE records.
>
> To make it scarier, this is NOT consistent; sometimes we get the above
> behaviour, and then for a few minutes the second query will return
> fewer records, before reverting back to the same incorrect results
> again:
>
> -----------
> 3804049
>
> (1 row(s) affected)
>
> -----------
> 3753145
>
> (1 row(s) affected)
>
> -----------
> 3751486
>
> (1 row(s) affected)
>
> The incorrect results are the norm, though.
>
> I've dropped / recreated all indexes in case one of them was corrupt -
> made no difference.
> We've DTS'd the table to another server, and failed to reproduce the
> problem.
> We've BCP'd the data to another server and failed to reproduce the
> problem.
> We're currently recreating a copy database by attaching a copy of the
> data file taken last night from the production db, in the hope that a
> binary copy will enable us to reproduce it outside the production
> server. If we can do that, then we can play around with the data a bit
> more.
>
> We're 99% sure the problem lies in the NULL values being held in the
> datearchived column, but we aren't sure exactly what is wrong with
> them.
>
> Has anyone seen anything remotely like this before? Anyone got any
> further ideas we could borrow?
>

Dan - thanks for that. Looks like it was spot on. We'd already
managed to get around the problem by dropping a covered index (which
changed the query plan to a clustered index scan, which for some reason
seems to have got rid of the problem).

Longer-term, I prefer your MAXDOP suggestion for this, since that
would enable us to reinstate the index - we've tested this and it does
work.

Thanks again.