SQL Server Performance Forum – Threads Archive

Problem with Select Count(*)

We are having a problem when doing a Select count(*) on a table. The result returns the wrong number of rows. When I do a select * on the table it returns the correct number, or a select count(Column) where the column is not the primary key. If it is the primary key it returns the wrong value. If I do a select count(*), count(column) then I receive the correct value. I have run a CheckDB with no errors and this shows the correct lower number of rows. The Select count(*) shows about 9.1 million rows, where as there is only 5.7 million rows in the database. In maintenance tonight I plan to update statistics. Is there anything else I should do or try?
Thanks.

Do you use any criteria? Any joins? Statistics shouldn’t matter, as you’re instructing the database engine to do a count, and that’s exactly what the engine is going to do. Seeing that this is your first post (welcome!) – just in case you’re counting records in a table in an MDB file (the MS Jet data format, as used my MS Access a.o.) you might get incorrect counts when the PK index is corrupted. Not sure if SQL Server would send you a warning instead of returning a false count, or not …

This was just something we noticed yesterday, when trying to verify a data merge. It is SQL Server 2000. There aren’t any criteria or joins on the select. There was just some concern about the counts being off.

When you say PK, is there actually a PK constraint on it, or is this just some field which you refer to as the PK because its supposedly unique? Sorry to ask, Ive seen this happen in the past <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Are you aware count ignores NULL values ? I was just wondering if your problem could be related to that. As in, if theres no constraint then maybe your pseudo-PK field has NULLS?

The PK is truly the Primary Key. I am aware that count will ignore the NULLs, that was one of the first things I checked when the developer brought this to me, I figured it was that also. But the select * from table only returned 5.7 million rows and the CHECKDB showed only 5.7 rows, but select count(*) showed over 9 million.
Thanks.

Also, I took this table via DTS to another database. Ran the select count(*) there and received the correct answer.

Hmm, sounds very strange. Ive never seen this problem, although I seem to vaguely recall someone else having it a long time ago. I dont remember the solution sadly. I think at this point I would first take a full backup of the DB and keep it safe. Then run a DBCC INDEXDEFRAG on the clustered index for that table. My hunch is that the table or index is perhaps damaged, but not in such a way that is detectable by the checks… I dont know how feasible this is but Im all out of ideas <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Have you noticed it on any other tables? Do you have torn page detection enabled on your database?

Haven’t noticed on any of the tables I have checked. Torn Page Detection is turned on. I also noticed if I click properties in Enterprise Manager, it shows the correct (lower) number of rows.

Consider this just a wild guess.
Do you have any FLOAT or REAL columns on that table? I’ve seen cases where such data looks like "1,#INF" and COUNT(*) and SELECT * produce different results. However, in most cases it was just the other way round in that SELECT * had te wrong number, while COUNT(*) was correct.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Also, run checkdb for integrity.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I ran checkdb and everything was fine. I rebuilt the PK index and still had a problem. I rebuilt all indexes and this has SOLVED the problem! Thanks everyone for the help.

Still strange, but good to hear it is solved. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thats a good shot by Luis, then ensure to maintain the optimization jobs as a scheduled jobs regularly to keepup the optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>