Are NULL Counts reliable?

select count(*) from Table(nolock)
-- 11972198
select count(*) from Table(nolock) where Vh_SummVeh_id is null
-- 11894085
select count(*) from Table(nolock) where Vh_SummVeh_id is not null
-- 9546220
...doesn't quite make sense, the collation of the field is NULL and datatyp
e
is int. The entire table has 11972198 records.
also one would think that the below:
select count(*) from Table(nolock) where Vh_SummVeh_id <> null
-- 0
would ave been the same as:
select count(*) from Table(nolock) where Vh_SummVeh_id is not null
-- 9546220
Are Null counts reliable?> [snip]
> ...doesn't quite make sense, the collation of the field is NULL and dataty
pe
> int. The entire table has 11972198 records.
Why did you use NOLOCK here? If the table is being updated then that
may explain the inconsistent results. If not I suggest you try running
DBCC CHECKTABLE.

> select count(*) from Table(nolock) where Vh_SummVeh_id <> null
> -- 0
This is perfectly correct. "<> NULL" returns an UNKNOWN result in every
case. Lookup the basics on NULLs and three-value logic in Books Online.
David Portas
SQL Server MVP
--|||Thanks but the table is not being updated so I shouldn't have had nolocks.
So it still doesn't make sense even after looking at DBCC CHECKTABLE
('TableName') which basically tells me that 'there are 11972198 rows in
800038 pages for object 'TableName''
I need to use a filterwithin a program on this particular field being NULL
so it wont be too good if I cannot rely on the output? Any more ideas?|||marcms
The difference between ANSI sql standard and sql server
SELECT WHERE SALARAY = NULL -- WRONG IN ANSI
SELECT WHERE SALARAY IS NULL-- RIGHT IN ANSI
SO IS
SELECT WHERE SALARAY <> NULL -- WRONG IN ANSI
SELECT WHERE SALARAY IS NOT NULL-- RIGHT IN ANSI
but for update
In ansi sql you have to use = null only
however sql server accepts when you set
SET ANSI_NULLS OFF then null = null
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> Thanks but the table is not being updated so I shouldn't have had nolocks.
> So it still doesn't make sense even after looking at DBCC CHECKTABLE
> ('TableName') which basically tells me that 'there are 11972198 rows in
> 800038 pages for object 'TableName''
> I need to use a filterwithin a program on this particular field being NULL
> so it wont be too good if I cannot rely on the output? Any more ideas?
>|||thanks, I checked that and still no joy.
Is it possible that if stats on 'TableName' were not recently updated, it
could cause this issue?
Also how can I see what the ansi nulls property is set to currently?
This one is weird, don't you think. Personally I have never seen it before!|||yup. It may statistics problem. Update statistics
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:
> thanks, I checked that and still no joy.
> Is it possible that if stats on 'TableName' were not recently updated, it
> could cause this issue?
> Also how can I see what the ansi nulls property is set to currently?
> This one is weird, don't you think. Personally I have never seen it before![/color
]|||No. It isn't statistics or fragmentation either!!!
Now I really am stumped.
My simple premise was that if you have 10million records from a count(*)
& 6million from a count(*) where Col1 IS NULL the you should get 4million
where Col1 IS NOT NULL.
Makes good enough sense to me. Any other ideas?|||You definitely should try the queries again without the nolock hint. If you
still have inconsistent
results, I would lean towards some corruption in the index used to drive the
query. You could check
the execution plans and see if forcing a table scan give different result. A
bug in SQL Server is
possible as well, of course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:DDDE7B7C-234B-429E-9AA7-9B8DABC80399@.microsoft.com...
> No. It isn't statistics or fragmentation either!!!
> Now I really am stumped.
> My simple premise was that if you have 10million records from a count(*)
> & 6million from a count(*) where Col1 IS NULL the you should get 4million
> where Col1 IS NOT NULL.
> Makes good enough sense to me. Any other ideas?
>|||YUP. That should be the case.
To make pukka yourself try selecting records instead of count(*)-- Kludge
and compare two queries. and check.. funny. Something is better than nothing
.
But dont say nothing is better than nonsense.
--
Regards
R.D
--Knowledge gets doubled when shared
"marcmc" wrote:

> No. It isn't statistics or fragmentation either!!!
> Now I really am stumped.
> My simple premise was that if you have 10million records from a count(*)
> & 6million from a count(*) where Col1 IS NULL the you should get 4million
> where Col1 IS NOT NULL.
> Makes good enough sense to me. Any other ideas?
>|||Thank for your efforts guys, I am still working on this. Another point of
note is I have already inserted thses NULLS earlier when I created the colum
n
but I have also inserted additional NULLS as I have run a program that does
a
named insert into TableName through a cursor and I have added the value NULL
on each iteration. Could this have an impact? The maximum number or times
this cursor has inserted was 3000 records.
Aside: here is the spHelp on the Col1 in my Filter that checks the number of
NULLS I have...
Col_name Type Computed Length Prec Scale Nullable TrimTrBlanks
FixedLenNullInSource Collation
-- -- -- -- -- -- --
-- -- --
Col1 int no 4 10 0 yes (n/a)
(n/a) NULL
I have to admit I am getting a bit desperate now but appreciate all your
inputs