469,167 Members | 1,238 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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

Jul 20 '05 #1
5 1250
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

Jul 20 '05 #2
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

Jul 20 '05 #3
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.


Jul 20 '05 #4
> 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.

Jul 20 '05 #5
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.

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by andrea | last post: by
1 post views Thread by John Wharmby | last post: by
10 posts views Thread by Bernard Liang | last post: by
73 posts views Thread by Rajeet Dalawal | last post: by
6 posts views Thread by Terry Carroll | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.