Bug? Select Count returns different counts - Why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bug? Select Count returns different counts - Why?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-05-15 : 16:46:07

We recently applied service pack 3 to our SQL 2000 db servers. I'm not sure if this issue is related to the service pack, but we found that when the following set of select statements are executed we get different results.

select count(*) from Agent..History where policynumber is null
select count(*) from Agent..History where policynumber is null
select count(*) from Agent..History where policynumber is null

If I remove "is Null" from the where clause, the count works ok.
When we replace * with Policynumber and include "is NULL" the problem occurs again.

The table contains about 4GB of data. When I run the same statement on smaller tables I do not encounter the problem.

The stats below show how "scan count" and "logical reads" change for each execution. Any thoughts on what is happening?

-----------
8784601

Table 'CommTranHist'. Scan count 131, logical reads 68127, physical reads 0, read-ahead reads 0.

-----------
8321135

Table 'CommTranHist'. Scan count 58, logical reads 65174, physical reads 0, read-ahead reads 0.

-----------
8851468

Table 'CommTranHist'. Scan count 88, logical reads 68337, physical reads 0, read-ahead reads 0.

Thanks, Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 16:50:06

Well maybe the data is changing before/during/after each execution. Is your application running or any process that modifies data in the history table running when you do these counts?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 18:01:33

If this were my problem, I'd write a query to return more information
- total rows
- total non null
- total null

Select count(*) as total,

sum(case when policynumber is not null then 1 else 0 end) as TotalNotNull,

sum(case when policynumber is not null then 0 else 1 end) as TotalNull

FROM Agent..History

I wonder if this query returns consistent results and how does the null count compare to your prior values?

How long does this query take to execute?

Sam

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-05-18 : 18:42:49

I tried dropping the non clustered index to see if it was corrupt. Now I'm just confused why I would receive different results with an index as opposed to without an index. The results should be the same regardless of the use of an index. One other note, I received these results when nobody else was connected to the server. Any thoughts?

select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
go
--
-- -----------
-- 12098955
-- -----------
-- 11957184
-- -----------
-- 12098955

Drop Index history.NCI_history4
go

select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
go

-- -----------
-- 4049359
-- -----------
-- 4049359
-- -----------
-- 4049359

CREATE INDEX [NCI_history4] ON [dbo].[history]([PolicyNumber]) ON [PRIMARY]
GO

select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
select count(*) from Agent..history where policynumber is null
--
-- -----------
-- 12117941
-- -----------
-- 12117941
-- -----------
-- 12117941
--

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-18 : 19:20:34

I'd recommend dropping all of the indexes on that table and running DBCC CHECKTABLE or even DBCC CHECKDB. See if any errors are reported. If not, then recreate the indexes (do the clustered index first) If you do get errors, you should export the data, drop the table, recreate it and import the data back into it.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-05-18 : 19:28:37

See if this is the issue - you could confirm by using OPTION(MAXDOP 1)

FIX: A Parallel Query with a COUNT Aggregate Function May Return Unexpected Results
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;814509[/url]

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources