Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

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

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
mmsi
Asked:
mmsi
  • 5
  • 3
  • 2
  • +3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Script out the Stored Procedure code and paste it into this question.  
Then look for an ORDER BY clause, or absense of one.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Haris DjulicCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ste5anSenior DeveloperCommented:
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
 
ZberteocCommented:
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
 
mmsiAuthor Commented:
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
 
ZberteocCommented:
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
 
mmsiAuthor Commented:
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
 
ZberteocCommented:
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
 
mmsiAuthor Commented:

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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
mmsiAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
mmsiAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now