Tell me more ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite

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
8  
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
4  
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
1  
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

1 Answer

up vote 4 down vote

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
I have not run dbcc checkdb, but I did find a nightly job defined on the server which the DB owners had apparently created. Step 1 runs DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS on every database on the server. Step 2 does a full backup on every database. – sworisbreathing Dec 16 '11 at 16:14
show 2 more comments

Your Answer

 
discard

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.