is my table hosed?

SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?
It's possible that another process was deleting rows while you ran your
queries.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
SELECT COUNT(*) FROM mytable
SELECT * FROM mytable
SELECT * FROM mytable ORDER BY mytableID
I'm getting back three different counts (using query analyzer) from
the above queries.
The first returns the value 228.
The second returns 160 rows of data.
The third returns 94 rows of data (all rows have a valid mytableID).
WTF? If I use Enterprise Manager and look at the table, I can see all
the rows (I think) and it looks OK. This query:
SELECT * FROM mytable WHERE mytableID = ID
returns the correct 2 rows I expect, although they are not listed in
either query above.
Any clues? Is the table index screwed up? Is that fixable?
|||On Jan 17, 9:33 pm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
How about Query analyser set to restrict the records returned - so
when you do a SELECT * FROM you get the reduce count. The will get the
correct count when you do SELECT COUNT(1).
Also, you could have the SET ROWCOUNT set to 160.. but that would just
weird.

> The rst returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?
|||On Jan 17, 1:33Xpm, "bryan...@.hotmail.com" <bryan...@.hotmail.com>
wrote:
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
Found the issue... Bad data in some numeric columns. Some #INF, etc.
values that shouldn't have been there. Saw the error message from
QueryAnalyzer when I finally bothered to click on the Messages tab.
You'd think they would have made this more obvious... :P
|||Are you in READ UNCOMMITTED isolation level, by any chance?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
<bryanp10@.hotmail.com> wrote in message
news:e7f0c0e4-cc20-4400-ab08-dd18745d70d1@.e10g2000prf.googlegroups.com...
> SELECT COUNT(*) FROM mytable
> SELECT * FROM mytable
> SELECT * FROM mytable ORDER BY mytableID
> I'm getting back three different counts (using query analyzer) from
> the above queries.
> The first returns the value 228.
> The second returns 160 rows of data.
> The third returns 94 rows of data (all rows have a valid mytableID).
> WTF? If I use Enterprise Manager and look at the table, I can see all
> the rows (I think) and it looks OK. This query:
> SELECT * FROM mytable WHERE mytableID = ID
> returns the correct 2 rows I expect, although they are not listed in
> either query above.
> Any clues? Is the table index screwed up? Is that fixable?