July 19, 2003 at 10:59 pm
All, Have anyone of you encountered with a situation where your SELECT * returns different NUMBER than SELECT COUNT(*).The table I queried is Large (11Million + records)and is frequently Updated & deleted.
We update the statistics nightly & Auto-Create & Auto Update are off. It has clustered PK and 3 Non Clust Indexes.
rowCount from sysindexes ,sp_spaceused,EnterpriseMGR,DBArtisan are obviously different from SELECT COUNT(*)..
But I never thought SELECT * would differ...I am kind of surprised to see SELECT * and SELECT COUNT(*) differing? Did anyone of you guys noticed this in u r environments.
I did a DB REINDEX and then the counts matched in all the above said patterns.
Can any one throw some light on this issue.
Thanks in advance for ur time
Ravi
July 21, 2003 at 9:32 am
No
But I tried to do a select count(*) and select * against a big and a little table and in both cases the execution plan used an 'index scan' to derive the results.
'The Index Scan logical and physical operator retrieves all rows from the nonclustered index specified ' (BOL)
So the two possible answers seem to be:
a) Someone changed your underlying data between you count(*) and your *
b) Your index pointers were a bit corrupt and your dbcc dbreindex command corrected them.
Although a) sounds more likely it sounds like your index pointers had a small amount of curruption, which was picked by the different queries.
As a total guess I'd say that widowed indexes (pointing to nothing) would bloat your count(*) value to be slightly higher than your select *, which would physically retrieve all the data. But I don't know.
Which was higher?
July 21, 2003 at 2:59 pm
sorry gor getting late to you as I was with my MGR proving before we open up Microsoft case.
Melvin, the count(*) is higher than Select * as u guessed.
But the a,b options cannot be considered as I reproduced the same problem this time in PROD environment.
This time we updated the STATS (Index + Columns) and counts matched.
For now we decided to go with the AUTO create on and watch if we get these Inconsistencies again.
Thanks for ur time..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply