Very puzzling count

[fn]

Does anyone have possible explanations for the following results?

select count(*) from tablename[color=blue][color=green][color=darkred]
>>> 2,500,000[/color][/color][/color]

select count(*) from tablename where fieldname is null[color=blue][color=green][color=darkred]
>>> 2,400,000[/color][/color][/color]

select count(*) from tablename where fieldname is not null[color=blue][color=green][color=darkred]
>>> 900,000[/color][/color][/color]

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

I'd appreciate any hints. Thanks.

fn

[John Bell]

Re: Very puzzling count

Hi

You could try DBCC CHECKDB to find any possible corruption.

John

"fn" <f_n_a_c_e_r_re moveunderlines@ hotmail.com> wrote in message
news:LqmdnWau8e KW1kfdRVn-gg@britsys.net. ..[color=blue]
> Does anyone have possible explanations for the following results?
>
> select count(*) from tablename[color=green][color=darkred]
> >>> 2,500,000[/color][/color]
>
> select count(*) from tablename where fieldname is null[color=green][color=darkred]
> >>> 2,400,000[/color][/color]
>
> select count(*) from tablename where fieldname is not null[color=green][color=darkred]
> >>> 900,000[/color][/color]
>
> Just my luck, I was showing something to the CFO when I got these
> results...
>
> I'd appreciate any hints. Thanks.
>
> fn
>[/color]

[Dan Guzman]

Re: Very puzzling count

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

"fn" <f_n_a_c_e_r_re moveunderlines@ hotmail.com> wrote in message
news:LqmdnWau8e KW1kfdRVn-gg@britsys.net. ..[color=blue]
> Does anyone have possible explanations for the following results?
>
> select count(*) from tablename[color=green][color=darkred]
> >>> 2,500,000[/color][/color]
>
> select count(*) from tablename where fieldname is null[color=green][color=darkred]
> >>> 2,400,000[/color][/color]
>
> select count(*) from tablename where fieldname is not null[color=green][color=darkred]
> >>> 900,000[/color][/color]
>
> Just my luck, I was showing something to the CFO when I got these
> results...
>
> I'd appreciate any hints. Thanks.
>
> fn
>[/color]

[fn]

Re: Very puzzling count

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:[color=blue]
> 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.
>[/color]

[Dan Guzman]

Re: Very puzzling count

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

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_n_a_c_e_r_re moveunderlines@ hotmail.com> wrote in message
news:65idnV9SD9 r83kbd4p2dnA@br itsys.net...[color=blue]
> 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:[color=green]
> > Check out MSKB 814509 to see if applies:
> >[/color][/color]
http://support.microsoft.com/default...09&Product=sql[color=blue][color=green]
> >
> > As a workaround, you can set 'max degree of parallelism' to 1 or specify[/color][/color]
a[color=blue][color=green]
> > MAXDOP 1 hint.
> >[/color]
>[/color]

[John Bell]

Re: Very puzzling count

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_n_a_c_e_r_re moveunderlines@ hotmail.com> wrote in message
news:65idnV9SD9 r83kbd4p2dnA@br itsys.net...[color=blue]
> 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:[color=green]
> > Check out MSKB 814509 to see if applies:
> >[/color][/color]
http://support.microsoft.com/default...09&Product=sql[color=blue][color=green]
> >
> > As a workaround, you can set 'max degree of parallelism' to 1 or specify[/color][/color]
a[color=blue][color=green]
> > MAXDOP 1 hint.
> >[/color]
>[/color]