We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another. After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.

I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?

  • 3
    Did the default collation change between the two servers? – John Eisbrener Apr 19 '17 at 19:16
  • @JohnEisbrener The instance-level collation shouldn't have anything to do with this issue. The database-level collation could potentially have an effect, but that's highly unlikely here since the DB was probably moved and would retain the same collation. The column-level collation is the most relevant to determining what happens with a column in a query, but without an ORDER BY no sorting will be attempted, unless the query required it for other reasons, such as a GROUP BY, but that wouldn't necessarily be reflected in the final results. – Solomon Rutzky Jan 24 '19 at 4:31
  • @SolomonRutzky I wouldn't take that question as an implied answer. I also asked this right around the time I was doing some work with a SSRS deployment where a vast majority of the reports were rendered from temp tables. If these reports are designed in a similar manner, instance-level collation may have an effect on ordering (although not guaranteed as properly identified in scsimon's answer below). – John Eisbrener Jan 24 '19 at 13:17
  • 1
    @JohnEisbrener Understood. I was clarifying just in case anyone was implying that it was a possible cause of this issue. And yes, with temp tables, if the collation isn't specified via the COLLATE clause (either in the CREATE TABLE statement or in the query), then instance-level collation can have an effect on what results are produced (comparisons affecting JOINs, GROUP BYs, etc). – Solomon Rutzky Jan 24 '19 at 15:07

If you don't have an ORDER BY clause, the order of the result set is never guaranteed.

Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.