Very puzzling count

[fn]

Does anyone have possible explanations for the following results?

select count(*) from tablename

2,500,000
select count(*) from tablename where fieldname is null 2,400,000
select count(*) from tablename where fieldname is not null 900,000

Just my luck, I was showing something to the CFO when I got these
results...

I'd appreciate any hints. Thanks.

fn

[John Bell]

Hi

You could try DBCC CHECKDB to find any possible corruption.

John

"fn" <f_**************************@hotmail.com> wrote in message
news:Lq********************@britsys.net...

Does anyone have possible explanations for the following results?

select count(*) from tablename

>>> 2,500,000
select count(*) from tablename where fieldname is null >>> 2,400,000
select count(*) from tablename where fieldname is not null >>> 900,000

Just my luck, I was showing something to the CFO when I got these
results...

I'd appreciate any hints. Thanks.

fn

[Dan Guzman]

Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default...09&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

"fn" <f_**************************@hotmail.com> wrote in message
news:Lq********************@britsys.net...

Does anyone have possible explanations for the following results?

select count(*) from tablename

>>> 2,500,000
select count(*) from tablename where fieldname is null >>> 2,400,000
select count(*) from tablename where fieldname is not null >>> 900,000

Just my luck, I was showing something to the CFO when I got these
results...

I'd appreciate any hints. Thanks.

fn

[fn]

Thanks for the replies.

The problem was definetely related to indexes and nulls rather than to
data curruption. I tried rebuilding the index but still got erroneous
results. On a hunch, I dropped all indexes and the query returned what
looked like correct results.

Dan, the MS article implies that the bug may not affect every
installation but doesn't provide any hints on how to test for it. Any
thoughts.

Thanks again.

FN

Dan Guzman wrote:

Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default...09&Product=sql

As a workaround, you can set 'max degree of parallelism' to 1 or specify a
MAXDOP 1 hint.

[Dan Guzman]

> Dan, the MS article implies that the bug may not affect every

installation but doesn't provide any hints on how to test for it. Any
thoughts.
Try recreating the index in question to see if the problem reoccurs. If you
get incorrect results with the nonclustered index in place, take a look at
the query plan and then repeat with the MAXDOP 1 hint.

I suspect the bug is the culprit if you get wrong results with a parallel
query and the expected results with parallelism off. You can either use one
of the workarounds I suggested or contact PSS for the hotfix.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"fn" <f_**************************@hotmail.com> wrote in message
news:65********************@britsys.net... Thanks for the replies.

The problem was definetely related to indexes and nulls rather than to
data curruption. I tried rebuilding the index but still got erroneous
results. On a hunch, I dropped all indexes and the query returned what
looked like correct results.

Dan, the MS article implies that the bug may not affect every
installation but doesn't provide any hints on how to test for it. Any
thoughts.

Thanks again.

FN

Dan Guzman wrote:

Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default...09&Product=sql
As a workaround, you can set 'max degree of parallelism' to 1 or specify a MAXDOP 1 hint.

[John Bell]

Hi

You may want to look at the query execution plans to see which/if it is
using an index. You may also way to try

SELECT fieldname, count(*) FROM tablename GROUP BY fieldname

John

"fn" <f_**************************@hotmail.com> wrote in message
news:65********************@britsys.net...

Thanks for the replies.

The problem was definetely related to indexes and nulls rather than to
data curruption. I tried rebuilding the index but still got erroneous
results. On a hunch, I dropped all indexes and the query returned what
looked like correct results.

Dan, the MS article implies that the bug may not affect every
installation but doesn't provide any hints on how to test for it. Any
thoughts.

Thanks again.

FN

Dan Guzman wrote:

Check out MSKB 814509 to see if applies:
http://support.microsoft.com/default...09&Product=sql
As a workaround, you can set 'max degree of parallelism' to 1 or specify a MAXDOP 1 hint.