Confused SQL Developer

[Moptop]

Hello
I am having problems with a query that I am developing on our Data Warehouse.
Briefly, the problem is this:
When I cound the number of records in a table with a null value, I get 1.3 million. If I count the number of records with a null value AND some other clause, I get more records. I think that I should ALWAYS get the same number of records or less. The 2nd query was originally using EXISTS - but this had the same result.
I am using SQL Server 2000.


Here is the SQL:

select count( * )
from dbo.imp_tblPerson p
where p.[HondaCustomerProspectIndicator] is null

Returns 1317608 records (i.e 1.3 million)



select count( * )
from dbo.imp_tblPerson p
where p.[HondaCustomerProspectIndicator] is null
and (select count(*) from dbo.imp_tblEntityProductRelationship eccr
where eccr.intEntityURN = p.intEntityURN
) > 0
Returns 1467258 records (i.e 1.4 million)


Thank you
Mark
Honda UK

 

[vongrunt]

Yes it should.

Are you absolutely sure results are correct? Perhaps new records were added in the meantime. Or you are looking at two different databases (this happened to me more than once). Or...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Moptop]

Hello
Thanks for coming back to me.
I am the only user logged on and I am looking at the same database.
The problem gets stranger still. I am trying to isolate the problem by eliminating parts of the query that could be the problem. The original query is about 100 lines long.

When I run this query:
select count( * )
from dbo.imp_tblPerson p
where (p.[HondaCustomerProspectIndicator] is null)
and exists (select top 1 'x' from dbo.imp_tblEntityProductRelationship eccr where eccr.intEntityURN = 2 )

I get different answers. Sometimes I get 2,181,528 records and sometimes I get 2,250,114 records. I know that both of these answers are wrong. Our DBA is backing up and restoring the database to a different server just to query that we don't have a SQL Server issue.
My guess is that there is a problem with the statistics for the tables.

Thanks
Mark

 

[vongrunt]

Stats shouldn't affect result, only execution plan.

Tech support (tm) way: if you stop/start server and try again, what happens?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Moptop]

Hi
The DBA is thinking that there is probably a SQL Server issue. One of the DLLs has been corrupted and he is taking the server down to fix it.
I guess that that explains it.
It is kind of worrying though - It was just a routine check of the data that highlighted it.
Thanks anyway
Mark