is null in where clause does not work properly

We have a very simple select statement:
select E.COURSE_YEAR, E.COURSE_SESSION, E.COURSE_CODE, E.FLAG
FROM enrollment e
where
e.course_year=2005
and e.course_session=2
AND E.FLAG IS NULL
There were 16743 records returned but more than half of them contain a value
in the FLAG field. What can be happening here? What can I check?
Funny enough (not really), when I take out the "AND E.FLAG IS NULL" clause,
there were 16778 records returned, but when I do "AND E.FLAG IS NOT NULL", I
got 7514 records, which is correct. I'm losing my mind over this.
We are running SQL Server 2000 w/SP3 on W2k3 standard edition.
We ran into a similar problem with a slightly more complex sql statement.
The strange thing is that if we ran the query 20 times we would probably
have the correct data returned to us somewhere between 2 and 4 times.
The .818 patch fixed the issue for us. As always, test the patch on a test
system first.
http://support.microsoft.com/kb/821277
Keith
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
> We have a very simple select statement:
> select E.COURSE_YEAR, E.COURSE_SESSION, E.COURSE_CODE, E.FLAG
> FROM enrollment e
> where
> e.course_year=2005
> and e.course_session=2
> AND E.FLAG IS NULL
> There were 16743 records returned but more than half of them contain a
> value
> in the FLAG field. What can be happening here? What can I check?
> Funny enough (not really), when I take out the "AND E.FLAG IS NULL"
> clause,
> there were 16778 records returned, but when I do "AND E.FLAG IS NOT NULL",
> I
> got 7514 records, which is correct. I'm losing my mind over this.
> We are running SQL Server 2000 w/SP3 on W2k3 standard edition.
|||Thanks for the reply. We haven't tried the patch yet. Luckily the system is
not in production yet and we still have time to play around. We found out
that it is a multi-processor issue - similar to the one documented for SQL
Server 7.0. When we assign SQL Server to use one processor only, the problem
goes away.
"Keith Kratochvil" wrote:

> We ran into a similar problem with a slightly more complex sql statement.
> The strange thing is that if we ran the query 20 times we would probably
> have the correct data returned to us somewhere between 2 and 4 times.
> The .818 patch fixed the issue for us. As always, test the patch on a test
> system first.
> http://support.microsoft.com/kb/821277
> --
> Keith
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
>
>
|||Just looked around and yes, the security fix you mentioned has that fix. I'll
give it a try next week.
Thanks.
"Keith Kratochvil" wrote:

> We ran into a similar problem with a slightly more complex sql statement.
> The strange thing is that if we ran the query 20 times we would probably
> have the correct data returned to us somewhere between 2 and 4 times.
> The .818 patch fixed the issue for us. As always, test the patch on a test
> system first.
> http://support.microsoft.com/kb/821277
> --
> Keith
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:E5958FF9-7B60-44D9-BCC0-E5FDF562AE8A@.microsoft.com...
>
>