Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Supposing I have a table foo similar to:

ident int, primaryKey, auto_increment
someNumber int
statusId int

where the someNumber column is, except for a few special cases, equal to ident. (in those special cases, someNumber is always equal to a previous ident).

I'm seeing problems which give the appearance that a query such as SELECT TOP 10 ident, someNumber FROM foo WHERE statusId=@desiredStatusId ORDER BY someNumber will return the wrong 10 records. The records all have the correct statusId, but randomly there is one whose someNumber is way off. For eaxmple:

ident        someNumber
-------      ----------
1234567      1234567
1234700      1234700
1234568      1234568
1234569      1234569
1234570      1234570
...          ...

What could be causing this query to intermittently return the wrong results?

share|improve this question
The situation you describe (example results and query) can't happen unless you have found a bug in SQL Server. More likely you have left out some vital detail in your description. Are you in actual fact using this query inside a table expression (e.g. view, cte, derived table) and expecting the order by to be respected by the outer query? – Martin Smith Oct 21 '11 at 17:12
Use gist to post a script with the CREATE TABLE statement, INSERT statements to populate the table, and the SELECT that is returning incorrect results. Chances are we'll immediately be able to find the problem or, even better, you'll see it yourself. – Nick Chammas Oct 21 '11 at 18:04
The query is used directly by my application. It is not used as a subquery, view, derived table, etc. We have used it many times a day for several years and have heard of no such problems until very recently. As such, I believe this problem is rooted in something environmental, and I am almost certain the database has not received proper maintenance in a very, very long time (if ever). – sworisbreathing Oct 24 '11 at 16:10

A corrupted index could do it. Have you run DBCC CHECKDB against that database?

share|improve this answer
Not yet. I'm a developer, not a DBA, and I'm coming at this from a standpoint of explaining why the system I wrote on top of the database is suddenly screwed up. This database is several years old. I helped develop it, but was never granted the authority to maintain it. I sincerely doubt that a competent DBA ever got anywhere near it. My boss looked at the execution plan for this query and found that the index used by it was 100% fragmented. We have made no code changes related to the query since before it messed up, so I thought it might be related to indexes. – sworisbreathing Oct 24 '11 at 15:49
@sworisbreathing fragmented<>corrupted and based on the information you've given, no code changes could produce this sort of effect. I'm not sure from your comment whether you are able to take Brent's advice or not, can you clarify? – Jack Douglas Oct 24 '11 at 17:27
I'm not able to run dbcc checkdb yet. I don't own the database, so there's a bit of red tape involved in letting me perform maintenance tasks on it. – sworisbreathing Oct 24 '11 at 18:21
@sworisbreathing ok, perhaps you should point the DB owners here as I'm sure they'd like to know if there is even the possibility of corruption - I know I would – Jack Douglas Oct 25 '11 at 8:41
@sworisbreathing Depends on the setup of the job - in each step, you can choose whether you'd like to continue or stop based on that step's success or failure. – Brent Ozar Dec 16 '11 at 16:30

looks like some one made an error while dml statement , other wise it shouldnt have given you that error. if you do a select statement and check the order of the number after 1234567 is 1234700 , then it is definitely insert statement error.

share|improve this answer
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. - From Review – Julien Vavasseur Jan 23 at 2:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.