Solved

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

Posted on 2014-10-10
14
106 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 49

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 33

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
 

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 49

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 33

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

679 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