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 (https://blog.sqlauthority.com)
16 Comments. Leave new
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.
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.
Is there any function used which returns random values every time like RAND() or NEWID()?
Check nullable value in where cluase columns
Does the query has reference to any temp tables.??
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.
Is the query time based?
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.
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.
It could be that there is a function in the code that is time based. I blogged about this occurring here –
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.
Make sure you are running the query in same sql server environment ( DEV, TEST, PROD) .
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.
I am running into this situation today. Each time I run the query the result set (over 100,000 rows) has a variance of 6 – 20 rows. The database being queried is refreshed only once a day. The query is comprised of 2 CTEs, a row_number function, partition function which orders the partition by date descending in DESC. I will review above materials.
I have come across a weird scenario. We have Microsoft RMS Deployed on POS Machines and the clients requirement was that the Offline DB (SQL Express)of POS Machines to be uptodate. So we created a stored procedure to get the data from the Linked server (SQL Server) and update the db. But every time the number of records transferred where different. I was able to recreate the same scenario that we are facing by executing the data transfer (which normally takes 25 seconds) then disconnect the network cable and after few seconds connect again. There will be no error thrown by SQL Server and it will show success. but the records transferred will be only partial. If any one is able to resolve this please post back.