SQL Server Performance Forum – Threads Archive

Weird problem involving nulls and counts

I have a fairly large database that is currently sitting around 200GB. Most of the space is used by 1 larger table, including it’s indexes, that accounts for about half of the space in the DB. The table has 69,787,491 rows comprised of 28 fields, with most being small char or int fields that relate to lookup tables. The problem I have is that when I query the data evaluating nulls on 2 fields I get different results. For instance consider the following queries, assuming Field1 is a CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> and Field2 is a DATETIME datatype. SELECT COUNT(*) FROM myTable WITH (NOLOCK) WHERE Field1 IS NOT NULL AND Field2 IS NULL This query always returns 7,575,060. (Which was not expected) SELECT * FROM myTable WITH (NOLOCK) WHERE Field1 IS NOT NULL AND Field2 IS NULL This query returns an empty result set. (Which is expected) SELECT COUNT(Field1) FROM myTable WITH (NOLOCK) WHERE Field2 IS NULL This query returns 0. (Which is expected) SELECT COUNT(*) FROM myTable WITH (NOLOCK) WHERE Field1 ordno IS NOT NULL AND COALESCE(Field2, ”) = ” This query returns 0. (Which is expected) I am at a loss to explain why the first query returns ~7 million rows when the other queries show that there are no rows meeting those criteria. If anyone has any explanations as to why this is happening or can suggest a fix for this discrepancy I would appreciate it. Thanks, Nate

There are a few things to think about when using count. For example COUNT(*) is not the same as COUNT(Field1) because COUNT(*) will count all rows when COUNT(Field1) will only count all non-null rows. And when you add that up with NULL checks in the where clause the results get more interesting <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> Example table: <pre> c1 c2 ——– ———- x 2003-04-02 x 2003-04-02 x NULL NULL NULL NULL 2003-04-02 x NULL x 2003-04-02 x 2003-04-02 x 2003-04-02 x 2003-04-02 </pre> Then run the following queries: SELECT COUNT(*) FROM myTable WITH (NOLOCK) WHERE c1 IS NULL SELECT COUNT(c2) FROM myTable WITH (NOLOCK) WHERE c1 IS NULL SELECT COUNT(c1) FROM myTable WITH (NOLOCK) WHERE c1 IS NULL Query 1 will return 2 rows Query 2 will return 1 row Query 3 will return 0 rows As you can see, they are very different <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> (A warning sign is the errors you get in QA: Warning: Null value is eliminated by an aggregate or other SET operation.) In your example you have additional null conditions on more colums in your where clause (and maybe that mess it up) but to me it looks like the first query returns correct result but the others don’t. If I run your queries on the example table above they all return 2 rows. What version of SQL are you running? I tested the above on SQL 2000 SP3. /Argyle

I understand about using * or a field in the count clause, which is why it really confused me as to why these 2 queries would return different results: SELECT COUNT(*)
FROM myTable WITH (NOLOCK)
WHERE Field1 IS NOT NULL
AND Field2 IS NULL SELECT COUNT(Field1)
FROM myTable WITH (NOLOCK)
WHERE Field2 IS NULL In theory they should both return the same count I believe. If the first query (that returns a count of ~7 million) is correct, then why would the second query in my first post, that simply tries to select those rows instead of counting them, return an empty result set? I have tried running the same queries on some of my other databases, other tables in the same database, and even other databases on other servers, and they always come out with matching results on those. It appears that it is only this one table that is giving me different results. If it helps I am running SQL 2000 Enterprise SP3 on Win 2000 Enterprise in a 2 node Active/Passive cluster. The results are the same no matter which node of the cluster I run the queries on. Thanks for your help so far, any other ideas?

Do you get any "null value is eliminated" warnings on any of the queries? /Argyle

Only in this query: SELECT COUNT(Field1)
FROM myTable WITH (NOLOCK)
WHERE Field2 IS NULL I believe that the warning should be expected in this instance however, because of how the COUNT(Field) works.

Hmm. I got no good answer to your problem. It simple seems strange that the results are different. /Argyle

Well thanks for trying Argyle. I have scoured MSDN and elsewhere on the web, but to no avail. We have a call with Microsoft support coming up next week to discuss some issues with clustering, maybe I will see if they have any ideas.

]]>