SQL SERVER – Monday Morning Puzzle – Query Returns Results Sometimes but Not Always

The amount of email I receive sometime it is impossible for me to answer every email. Nonetheless I try to answer pretty much every email I receive. However, quite often I receive such questions in email that I have no answer to them because either emails are not complete or they are out of my domain expertise. In recent times I received one email which had only one or two lines but indeed attracted my attention to it. The question was bit vague but it indeed made me think. The answer was not straightforward so I had to keep on writing the answer as I remember it. However, after writing the answer I do not feel satisfied. Let me put this question in front of you and see if we all can come up with a comprehensive answer.

Question: I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.

Well, if you read the question, it is indeed incomplete and it does not contain much of the information at all. I decided to help him and here is the answer, which I started to compose.

Answer: As there are not much information in the original question, I am not confident what will solve your problem. However, here are the few things which you can try to look at and see if that solves your problem.

  • Check parameter which is passed to the query. Is the parameter changing at various executions?
  • Check connection string – is there some kind of logic around it?
  • Do you have a non-deterministic component in your query logic? (In other words – does your result is based on current date time or any other time based function?)
  • Are you facing time out while running your query?
  • Is there any error in error log?
  • What is the business logic in your query?
  • Do you have all the valid permissions to all the objects used in the query? Are permissions changing or query accessing a different object in various executions?
  • (Add your suggestions here)

Meanwhile, have you ever faced this situation? If yes, do share your experience in the comment area. I will send a copy of my book SQL Server Interview Questions and Answers to one of the most interesting comment. The winner will be announced by next Monday. 

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

15 thoughts on “SQL SERVER – Monday Morning Puzzle – Query Returns Results Sometimes but Not Always

  1. I have experienced this when the query used was not deterministic. In my scenario we had row number functions that would not always yield the same result used in a CTE. This CTE was joined to multiple times. When the parameters were changed (date range expanded) the optimizer would take a different route to get the data and the row number function produced a different row order than expected. This made the join fail and no data returned.

    The solution was to use a better order by clause in the row number function.

  2. I would like to add these points,

    1. Whether the parameter value is being changed in anywhere in the SP/Query using SET or SELECT ?

    2. Check for the ALIAS names used in the QUERY to confirm that the ALIAS is not misplaced.

  3. I asked the question \”Could you please brief about Cursor in MS SQL Server ?\” to someone in a telephonic interview.

    He replied , Cursor is a same as a Mouse Cursor. We can use it whenever we require.

  4. One of the most obvious potential answers is still overlooked – is the underlying data changing such that the query is not always satisfied?

    So even if the query is deterministic and any input parameters are static, if the data is ‘coming and going’ then the query results would be inconsistent.

  5. I also had a different experience where query results were inconsistent, however it was related to a query to an Oracle database via a .NET application using the Oracle .NET data adapter. The problem was that depending on how the results were ordered (with order by clause) resulted in different result sets being returned (ie, different number of rows in the result set).

    It turned out the problem was due to a bug in the .NET data adapter for Oracle, provided by Oracle. The query had a computed column with a division, and sometimes the denominator would be a really small value, and this caused the oracle data adapter to throw a ‘divide by 0′ exception. So when run directly from a utility such as Toad the blowup/exception would be displayed, however the calling .NET commercial client application absorbed the error (erroneously) and returned the results that had streamed in thus far (instead of an error!). Therefore changing the ‘order by’ resulted in the bad record showing up at a different spot in the query results, producing different sized result sets.

    The problem was reported to Oracle, but as far as I know they have not yet issued a fix.

    I know this is not a SQL Server issue, but since it is related to inexplicably varying query results and also with Microsoft .NET I thought it might be of interest to those who work with a blend of RDBMS in their environments.

    • Excellent Point – I wish I have found this link earlier – I should send to asker right away. I will update the post when I collate all the information and your solution link on the top. Excellent! Seriously great read!

  6. 1. Query hint WITH (NOLOCK) or transaction isolation level Read Uncommitted can produce unpredictable and not repeatable results. 2. In SSMS running queries in more than one window with uncommitted transaction(s) in some window(s) can produce confounding results.

  7. I have experienced this when I was executing simple delete statement on single table with where clause on one of the column from primary key with datatype int. this statement was deleting no row.
    I have copied the same query and executed on new query window it deleted all the rows satisfying the criteria.

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s