[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

is null plus is not null doesn't equal count

Charles Windhausen

3/22/2007 5:40:00 PM

I've a table with a very large number of rows. The [Version] column is
a bigint that is nullable. Here's the results of some queries:

select count(*) from Events
3214670

select count(*) from Events where Version is null
3105991

select count(*) from Events where Version is not null
1318812

What in the wide, wide world of sports is going on here? I expect the
sum of the two lower values should equal the first but 1318812 +
3105991 != 3214670. Instead it's equal to 4424803. Can someone explain
this discrepancy?

Thanks
Charles
10 Answers

Tom Moreau

3/22/2007 5:45:00 PM

0

Was there any INSERT or UPDATE activity during this period?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Charles" <cwindhausen@gmail.com> wrote in message
news:1174585206.429248.294990@n76g2000hsh.googlegroups.com...
I've a table with a very large number of rows. The [Version] column is
a bigint that is nullable. Here's the results of some queries:

select count(*) from Events
3214670

select count(*) from Events where Version is null
3105991

select count(*) from Events where Version is not null
1318812

What in the wide, wide world of sports is going on here? I expect the
sum of the two lower values should equal the first but 1318812 +
3105991 != 3214670. Instead it's equal to 4424803. Can someone explain
this discrepancy?

Thanks
Charles

AK

3/22/2007 5:59:00 PM

0

On Mar 22, 12:40 pm, "Charles" <cwindhau...@gmail.com> wrote:
> I've a table with a very large number of rows. The [Version] column is
> a bigint that is nullable. Here's the results of some queries:
>
> select count(*) from Events
> 3214670
>
> select count(*) from Events where Version is null
> 3105991
>
> select count(*) from Events where Version is not null
> 1318812
>
> What in the wide, wide world of sports is going on here? I expect the
> sum of the two lower values should equal the first but 1318812 +
> 3105991 != 3214670. Instead it's equal to 4424803. Can someone explain
> this discrepancy?
>
> Thanks
> Charles

one possible explanation here:

http://www.devx.com/dbzone/Article/3295...

Charles Windhausen

3/22/2007 6:28:00 PM

0

No updates, but I guess there may have been one or two inserts.
Certainly not 4424803 - 3214670 = 1210133 inserts though.

> Was there any INSERT or UPDATE activity during this period?
>
> --
> Tom
>

Charles Windhausen

3/22/2007 6:31:00 PM

0

I'm pretty sure that's not what is causing my issue. The records in
the Events table are only added and never changed. It's more like a
history or transaction table where I'm just recording events as they
happen, so there's no reason to ever use an Update on the table.

> one possible explanation here:
>
> http://www.devx.com/dbzone/Article/3295...

AK

3/22/2007 6:39:00 PM

0

On Mar 22, 1:31 pm, "Charles" <cwindhau...@gmail.com> wrote:
> I'm pretty sure that's not what is causing my issue. The records in
> the Events table are only added and never changed. It's more like a
> history or transaction table where I'm just recording events as they
> happen, so there's no reason to ever use an Update on the table.
>
>
>
> > one possible explanation here:
>
> >http://www.devx.com/dbzone/Article/32957... Hide quoted text -
>
> - Show quoted text -

I would not be so sure. If you want you numbers to match, run all your
selects in one transaction and use SERIALIZABLE or SNAPSHOT isolation.
The scenario I was describing is not the only one possilbe, just a
very easy to reproduce one. If you insert into the part of the table
already scanned by your select, and if you are using READ COMMITTED,
you can get discrepancies in your counts.

Charles Windhausen

3/22/2007 7:49:00 PM

0

I still get the same funny numbers, but here's another weird one:

select count(*) from Events where Version is null and Version is not
null
1210570

It's like I've stepped into a Far Side commic. How can this statement
return anything but zero?

> I would not be so sure. If you want you numbers to match, run all your
> selects in one transaction and use SERIALIZABLE or SNAPSHOT isolation.
> The scenario I was describing is not the only one possilbe, just a
> very easy to reproduce one. If you insert into the part of the table
> already scanned by your select, and if you are using READ COMMITTED,
> you can get discrepancies in your counts.

Tracy McKibben

3/22/2007 8:02:00 PM

0

On Mar 22, 2:48 pm, "Charles" <cwindhau...@gmail.com> wrote:
> I still get the same funny numbers, but here's another weird one:
>
> select count(*) from Events where Version is null and Version is not
> null
> 1210570
>
> It's like I've stepped into a Far Side commic. How can this statement
> return anything but zero?
>

What version and SP are you running? We had an issue here with SQL
2000 SP3a, involving indexes that included BIT columns. The issue was
manifested as inconsistent record counts, very similar to what you're
seeing. Look at the execution plan for this last query you posted -
is it scanning an index? Try forcing a table or clustered index scan.

Charles Windhausen

3/22/2007 8:38:00 PM

0

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

This appears to be an issue with the indexes. I've dropped the three
indexes I have that included the Version column. Now all my queries
are returning good numbers. I'll rebuild the indexes and see if the
issue comes back. Thanks for the idea, Tracy.

> What version and SP are you running? We had an issue here with SQL
> 2000 SP3a, involving indexes that included BIT columns. The issue was
> manifested as inconsistent record counts, very similar to what you're
> seeing. Look at the execution plan for this last query you posted -
> is it scanning an index? Try forcing a table or clustered index scan.

Charles Windhausen

3/22/2007 8:52:00 PM

0

I've added a non-clusterd index on Version asc. And I get funky
numbers again.

select count(*) from Events where Version is not null and Version is
null
1262861

Is there something I'm not understanding about indexing columns that
are nullable?

Charles

Tracy McKibben

3/23/2007 1:23:00 PM

0

On Mar 22, 3:51 pm, "Charles" <cwindhau...@gmail.com> wrote:
> I've added a non-clusterd index on Version asc. And I get funky
> numbers again.
>
> select count(*) from Events where Version is not null and Version is
> null
> 1262861
>
> Is there something I'm not understanding about indexing columns that
> are nullable?
>
> Charles

If the query going parallel? This might apply:
http://support.microsoft.com/kb/81...