COUNTING PROBLEM !!!

[Olaf Bönning]

I have a serious problem: Each time I run the following
query the count result is changing!

SELECT count(*) from posnad WHERE lfn_pos IS NULL

lfn_pos is defined as an 'int' and used as a foreign key.
The table is quite big (8,000,000 rows) and the count
result should return about 1,600,000 rows.

There are NO updates, insertions, deletions, triggers or
what ever on the table.
I am the only user. Transaction isolation level is READ
COMMITED and a already tried the SET NOCOUNT option.

Any ideas???

Regards,

------------
Olaf Bönning
IT Specialist

[Itzik Ben-Gan]

Olaf, you'd be surprised how many times people were sure that nothing modified their tables and eventually found out they were wrong.
Try running a Profiler trace to monitor modifications against the table (you can set a filter on objectid).

--
BG, SQL Server MVP
Solid Quality Learning
[url]www.solidqualitylearning.com[/url]


"Olaf Bönning" <Olaf.Brecht@gmx.net> wrote in message news:050201c3444c$ba68cee0$a301280a@phx.gbl...
I have a serious problem: Each time I run the following
query the count result is changing!

SELECT count(*) from posnad WHERE lfn_pos IS NULL

lfn_pos is defined as an 'int' and used as a foreign key.
The table is quite big (8,000,000 rows) and the count
result should return about 1,600,000 rows.

There are NO updates, insertions, deletions, triggers or
what ever on the table.
I am the only user. Transaction isolation level is READ
COMMITED and a already tried the SET NOCOUNT option.

Any ideas???

Regards,

------------
Olaf Bönning
IT Specialist

[Greg Obleshchuk]

Olaf,
The set nocount will not effect the count in your query
Try this
SELECT lfn_pos, count(*) as count from posnad group by lfn_pos
this will count per grouping of lfn_pos
Is posnad a table or query?

What version of SQL are you using?

Basicall if there really are no changes to the table then the fighures
should remain the same, I don't remember reading about any bugs like this in
the latest SP (I asume your on the latest SP?)

If posnad is a view then mayb the view details are changing?


--
I hope this helps
regards
Greg O MCSD
SQL Scribe Documentation Builder
Document any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
[url]http://www.ag-software.com/ags_scribe_index.asp[/url]



"Olaf Bönning" <Olaf.Brecht@gmx.net> wrote in message
news:050201c3444c$ba68cee0$a301280a@phx.gbl...
I have a serious problem: Each time I run the following
query the count result is changing!

SELECT count(*) from posnad WHERE lfn_pos IS NULL

lfn_pos is defined as an 'int' and used as a foreign key.
The table is quite big (8,000,000 rows) and the count
result should return about 1,600,000 rows.

There are NO updates, insertions, deletions, triggers or
what ever on the table.
I am the only user. Transaction isolation level is READ
COMMITED and a already tried the SET NOCOUNT option.

Any ideas???

Regards,

------------
Olaf Bönning
IT Specialist

[Olaf Bönning]

I started the execution plan in Query Analyzer and found
out that the numbers are calculated using an index scan
and not a table scan. Could my index be corrupt?

------------
Olaf Bönning
IT Specialist