proc gets different result on different servers -- looses sort 1

[dkaplan]

I have a proc in MS SQL that behaves differently depending on which server the proc is run.

Proc B calls proc A which returns a sorted a table. Proc B takes the top 1 record from the sorted table.

On our production server, the sort is preserved and we get the expected record.

In our test environment (basically a clone of production) the sort is lost and we appear to get a random record.

Here is an excerpt of the code from the calling procedure:

SELECT TOP 1 variable1, variable2
FROM [dbo].udf_MyUserDefinedFunction()


Please note:
1. I have checked settings on both servers, also SQL settings, and see no significant differences. Both production and test are running (SQL 2012) . The compatibility level of Test is 110. In theory, production is the same, but I am unable to confirm this.

2. The obvious solution of enforcing the sort in the calling procedure is not available to me. I do not have access to the code. At this time, I am only being asked to explain why our test and production environments are getting a different result.

 

[gmmastros]

Without an order by, select top 1 should never be trusted, and this is exactly why... sometimes it works the way you want it to, and sometimes it doesn't.

Basically, SQL server has decided to use different execution plans on each server. One execution plan is using an index that has the data sorted the way you want it, and the other does not. There are several potential reasons for this. The indexes you have on the tables could be different or your statistics could be out of date, or you could have a lot more data in one DB than another, or..... the list goes on.

If your front end is performing a select top 1 from a table valued function, you could try putting the order by inside the table valued function.

Regardless of how you fix this, you should never rely on sorting without an order by.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

To extend gmmastros’ point
Someone once said that the records in the data base are like marbles on the back of the truck. You never know what order they will come to you unless YOU specify the order.
The hard part is: 90% of the time you may get them the way you want them, so it is easy to ignore the Order By part of your Select statement.


Have fun.

---- Andy

There is a great need for a sarcasm font.