Solved

Microsoft SQL 2012 Stored Procedure returning records in a different order every time it is executed

Posted on 2014-10-10
14
97 Views
Last Modified: 2014-10-10
We recently upgraded to Microsoft SQL 2012, and are now experiencing some issues with the Query results.  

Each time we call a stored procedure it returns the results in a different order.

examples:

First time we call the stored procedure it will return:
1
2
3

Second time we call the stored procedure it will return:
3
1
2

Another strange thing is, we have a live database and a test database.  I was testing a stored procedure on the live database, and it returned all records in the correct order every time, but the same stored procedure on the test database returned the results in a different order.  Please note, I'm not sure why this specific stored procedure is returning the correct results in the correct order on the live database, because there are other stored procedures on the live database that are returning records in different order each time its executed.

I'm not sure why this is happening, It never happened on Microsoft SQL 2008.  The project is huge so before making changes to the queries I came here to seek advice.
0
Comment
Question by:mmsi
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40372923
Script out the Stored Procedure code and paste it into this question.  
Then look for an ORDER BY clause, or absense of one.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40372935
Without seeing the SP code the only thing we can do is guess.
Check for difference between schemas on both databases (specially indexes). The SQL Server in live and test environments has the same version?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40372972
If no ORDER BY clause is used then :

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40373076
Jim andSamo are right.

As long as there is no ORDER BY in the final SELECT statement which generates the result of your store procedure, the order is unpredictable. Cause there are many factors involved, like clustered indices, the query plan, especially parallelism of execution.

It never happened on Microsoft SQL 2008

You've been lucky.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 40373105
Modify the stored procedure to include an ORDER BY at the end of the returning query.

When there is no ORDER BY there is no guarantee that you will get the same order every time even if you run it on the same box. Sometimes is happens just because the result is already cached in the memory that way but after a while it could change.

It helps with order without ORDER BY if the table has a clustered index. Maybe on your live box it exists but not on the dev one. However even with cluster index you could have unpredictable order without ORDER BY clause.
0
 

Author Comment

by:mmsi
ID: 40373116
Here is part of the code in the stored procedure that is being executed.
IF @cID = 6
BEGIN
SELECT tblFeatureIndex.ItemName AS cIDX
FROM tblCompPrint 
     INNER JOIN tblFeatureIndex ON tblCompPrint.fIDX = tblFeatureIndex.ItemIndex 
     INNER JOIN tblJobComponents ON tblCompPrint.cIDX = tblJobComponents.cIDX
     WHERE(tblJobComponents.JobID = @jID) 
     AND (tblJobComponents.CompTypeID = @cID) 
     AND (tblCompPrint.cIDX = @nVal) 
     AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
SELECT @comm=Comments, @cName = CompName
FROM tblJobComponents
WHERE(tblJobComponents.cIDX = @nVal) AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
END

Open in new window


This code has been in the system for years, way before me.  Has never had a problem even without the order by, until we switched to a new server.

Let me know if you need anything else,
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40373125
OK, try this:
IF @cID = 6
BEGIN
SELECT tblFeatureIndex.ItemName AS cIDX
FROM tblCompPrint 
     INNER JOIN tblFeatureIndex ON tblCompPrint.fIDX = tblFeatureIndex.ItemIndex 
     INNER JOIN tblJobComponents ON tblCompPrint.cIDX = tblJobComponents.cIDX
     WHERE(tblJobComponents.JobID = @jID) 
     AND (tblJobComponents.CompTypeID = @cID) 
     AND (tblCompPrint.cIDX = @nVal) 
     AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
SELECT @comm=Comments, @cName = CompName
FROM tblJobComponents
WHERE(tblJobComponents.cIDX = @nVal) AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
ORDER BY cIDX
END

Open in new window

The ORDER BY clause will solve you problem and every time the result will come in the right order on any box.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:mmsi
ID: 40373132
I can't use Order by without returning additional information this stored procedure is only returning Item name.

The program expects:
First record returned is for "Paper Stock"
Second record returned is for "Paper Size"
...
...
...

The program appears to want the information in the order that is was saved to the database.
IF @cID = 6
BEGIN
SELECT tblFeatureIndex.ItemName AS cIDX
FROM tblCompPrint 
     INNER JOIN tblFeatureIndex ON tblCompPrint.fIDX = tblFeatureIndex.ItemIndex 
     INNER JOIN tblJobComponents ON tblCompPrint.cIDX = tblJobComponents.cIDX
     WHERE(tblJobComponents.JobID = @jID) 
     AND (tblJobComponents.CompTypeID = @cID) 
     AND (tblCompPrint.cIDX = @nVal) 
     AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
SELECT @comm=Comments, @cName = CompName
FROM tblJobComponents
WHERE(tblJobComponents.cIDX = @nVal) AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
END

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40373150
Yes you can use ORDER BY, just try it. In terms of what the procedure should return that is an entirely different issue and we cannot help without extra info. Basically you are asking us to write your procedure...
0
 

Author Comment

by:mmsi
ID: 40373152

The SQL Server in live and test environments has the same version?

Yes it is the same version.  The database admin just made a copy on the same server for development purposes.
IF @cID = 6
BEGIN
SELECT tblFeatureIndex.ItemName AS cIDX
FROM tblCompPrint 
     INNER JOIN tblFeatureIndex ON tblCompPrint.fIDX = tblFeatureIndex.ItemIndex 
     INNER JOIN tblJobComponents ON tblCompPrint.cIDX = tblJobComponents.cIDX
     WHERE(tblJobComponents.JobID = @jID) 
     AND (tblJobComponents.CompTypeID = @cID) 
     AND (tblCompPrint.cIDX = @nVal) 
     AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
SELECT @comm=Comments, @cName = CompName
FROM tblJobComponents
WHERE(tblJobComponents.cIDX = @nVal) AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
END

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40373165
The program appears to want the information in the order that is was saved to the database
All Experts here are told you that's something that you can't control unless you have an ORDER BY clause.
This is a bad design model and now it's time to fix it. There's some workarounds depending in what side of the solution you want to change (application code or database code?).
Here's a solution with ORDER BY tblFeatureIndex.ItemIndex (I don't know if this helps but you can give a try):
IF @cID = 6
BEGIN
SELECT tblFeatureIndex.ItemName AS cIDX
FROM tblCompPrint 
     INNER JOIN tblFeatureIndex ON tblCompPrint.fIDX = tblFeatureIndex.ItemIndex 
     INNER JOIN tblJobComponents ON tblCompPrint.cIDX = tblJobComponents.cIDX
     WHERE(tblJobComponents.JobID = @jID) 
     AND (tblJobComponents.CompTypeID = @cID) 
     AND (tblCompPrint.cIDX = @nVal) 
     AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
ORDER BY tblFeatureIndex.ItemIndex 

SELECT @comm=Comments, @cName = CompName
FROM tblJobComponents
WHERE(tblJobComponents.cIDX = @nVal) AND (IsDeleted = CASE WHEN @showdeleted = 0 THEN IsDeleted ELSE 0 END)
END

Open in new window



How many items should this SP return?
0
 

Author Comment

by:mmsi
ID: 40373187
I understand what everyone is saying about the order by clause.  

I had to to modify some of my code in a few programs that I wrote, because the users were seeing this problem.  Now I'm starting to see the issue in our scheduling system.  It's not just one query and there are several places that the code is pulling stored procedures and pulling the records in the wrong order.  The project is huge.

I'm confused on the fact that:
1.  Some stored procedures are returning the records in the correct order without an order by clause
2. Some stored procedures are not returning the records in the correct order without an order by clause

Even without an "order by" we have not had a problem in the last 5+ years.  Not until we moved everything to a new server.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 40373245
This is a conceptual problem. A set per se has no order. So what is order? Order is the sequence in which rows are returned, when we return one row after the other.

Thus  SQL Server defines order as the last SELECT must have an ORDER BY clause. Without that, there is no order. Just because the data was returned in the correct order for your consumer does not mean that there is an ordererd set.

Cause anything which makes SQL Server to return data depends on the query plan. And the query plan must not be the same for every execution. So any inner order criteria or mechanism may be removed, reordererd or ignored be the query optimizer. Cause he only works with sets.

When you need a particular order then apply it. Maybe you need a complex CASE expression or even an helper table.
0
 

Author Comment

by:mmsi
ID: 40373250
I was hoping I would come here and u guys would tell me it was a setting on the server, but I see I have to start updating.

Thanks for all your help, and quick responses.

There is one answer here that I liked, and it appears to be working, I just have to update all the stored procedures that I really did not want to do.  

Since the program wants the information in the order they were entered into the system, I will do a order by clause on the tables indexes.

Order by tblCompPrint.idx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now