Counting NULLs

You are correct, we're running SP3. Although the Microsoft bug description doesn't seem to explain why "SELECT <nonkey field> FROM Table WHERE KeyField IS NULL" doesn't work (the bug only mentions aggregate functions), I'll assume it's the same issue. Looks like it's time to move to SP4.

If anyone want to check for the problem on their system, the most obvious symptom is that I get various random answers for SELECT COUNT(*) FROM Table WHERE Field IS NULL with every execution.

One workaround is to set MAXDOP=1, but these also work:

SELECT COUNT(*) - COUNT(Field) FROM Table

SELECT SUM(CASE WHEN Field IS NULL THEN 1 ELSE 0 END) FROM Table

Both of these alternatives force a full index scan and avoid the problem with the index seek.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ya but it kills the performance of the index seeks... maybe it's time to test SP4 in developement and see if it breaks anything else before applying it.

noeld SSC Guru Points: 96590 More actions · Answer 6

Can you check what Plan do you get NOW if by any chance you get Index spool there is another of these nasty bugs hidden in those waters

Like I said MAXDOP has been my workaround so far if you feel confortable with SP4 go for it - make sure you get the Post SP4 AWE hotfix if you need it

Isn't it amazing we already need a hotfix right after the realease of SP4

it looks like all efforts are being put on the 2005 release

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Can you imagine how much testing is required just to have that little bugs??? I wouldn't wanna have to manage all that work...

noeld SSC Guru Points: 96590 More actions · Answer 8

Me neither but they are the ones with the big $$$.

With Power comes responsibility

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Still, with a few hundred billions lines of codes, running a few thousands of possible install combinaisons or software/versions, you're gonna miss a bug. There's no way they can come up with a bug free product.

noeld SSC Guru Points: 96590 More actions · Answer 10

>> There's no way they can come up with a bug free product. <<

Absolutely correct but there are some bugs bigger than others. With todays XP technologies, regresssion tests environments and all the $$ you need to make it happen most of those BIG bugs could have been covered. I am not saying it is a small task but remember we are talking Microsoft here not a Mom and Pap shop

BTW I don't think is billions of line of code

millions maybe

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

There was a leak a few months/years back. The code was a 600+ mb ZIPPED file. That accounted for only 2% of the source code (win 2K).

It may not be billions, but it sure is 100M+.