INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

proc gets different result on different servers -- looses sort

proc gets different result on different servers -- looses sort

(OP)
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.

RE: proc gets different result on different servers -- looses sort

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

RE: proc gets different result on different servers -- looses sort

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close