Null in where clause problem

I'm getting a really bizarre problem. I have a table which consists of monthly billing data. It currently has just over 2 million rows in it.
I did a select on the table trying to get the number of rows where a column was null, for a particular billing period. Out of curiousity I did the same query for the not null columns, and then did a count for all the rows for that period.
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is null
select count(*) from tblbillingdetail
where periodid = 200312
and employeeid is not null
select count(*) from tblbillingdetail
where periodid = 200312
They didn't add up. The same was true for any other periodid I picked. Running the same query over the whole table (all periods) does add up, however.
I then went and tried the following query:
select distinct employeeid from tblbillingdetail
where periodid = 200312
and employeeid is null
It returns only one value for the employeeid, which changes every time I run it.
There are indexes on the PeriodID column and the EmployeeID column, which I ran a DBCC REINDEX on, but with no affect.
Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a Windows 2000 box, by the way.
Thanks,
Brendan.Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
As a workaround, you can set 'max degree of parallelism' to 1 or specify a
MAXDOP 1 hint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:48D0B9CA-D278-4E2B-BF20-3C724E4F2ACF@.microsoft.com...
> I'm getting a really bizarre problem. I have a table which consists of
monthly billing data. It currently has just over 2 million rows in it.
> I did a select on the table trying to get the number of rows where a
column was null, for a particular billing period. Out of curiousity I did
the same query for the not null columns, and then did a count for all the
rows for that period.
> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> select count(*) from tblbillingdetail
> where periodid = 200312
> and employeeid is not null
> select count(*) from tblbillingdetail
> where periodid = 200312
> They didn't add up. The same was true for any other periodid I picked.
Running the same query over the whole table (all periods) does add up,
however.
> I then went and tried the following query:
> select distinct employeeid from tblbillingdetail
> where periodid = 200312
> and employeeid is null
> It returns only one value for the employeeid, which changes every time I
run it.
> There are indexes on the PeriodID column and the EmployeeID column, which
I ran a DBCC REINDEX on, but with no affect.
> Has anyone seen this before? I'm using SQL Server 2000, with SP3, on a
Windows 2000 box, by the way.
> Thanks,
> Brendan.
>|||Thanks Dan. MAXDOP got it.
Regards,
Brendan.|||Glad it worked for you.
--
Dan Guzman
SQL Server MVP
"Brendan" <anonymous@.discussions.microsoft.com> wrote in message
news:B798638C-26F8-409C-862B-10E2F6FC25FB@.microsoft.com...
> Thanks Dan. MAXDOP got it.
> Regards,
> Brendan.
>