0

I am working on an old VB6.0 application that is being converted to C#. There are a number of SQL Server Views that are queried in the application. I am passing the same SELECT statements in VB6.0 and C# to query the Views. I am also providing exactly the same WHERE clause in each language, and no ORDER BY clause. The views do contain their own ORDER BY clauses.

In VB6.0, the result set consistently comes back in the same order, and in C# the result set consistently comes back in the same order. However, the orders returned to VB6.0 and C# do not match. I can query through VB6.0 and then query a few seconds later through C#, and the result sets are in different orders.

What could be causing the result set orders to be different? I am needing to compare some output files generated from the new C# code to the VB6.0 code for validation, so I would like to either have the result sets come back in the same order for both, or at least understand why the result set orders do not match between VB6.0 and C#, but always match each other when called from the same language.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Poosh
  • 532
  • 2
  • 10
  • 25

1 Answers1

12

If you don't specify an ORDER BY you are never guaranteed what order the records come back in.

The order may usually come back in an order related to a clustered index, or some other characteristic of the data/schema. But it can change, without warning, or reason that you can perceive.
- It might be due to which cores are or are not busy
- It might be due to the interface you use with the SQL Server
- It might be due to the records that were most recently introduced
- Or fragmentation
- Or caching of tables/results
- Or a patch on the SQL Server or OS


If the order matters, specify an ORDER BY on the final/outermost query.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • There is an ORDER BY specified by the underlying view..which may not work as intended. – D.R. Jul 30 '13 at 20:05
  • 1
    @DR The ORDER BY on the underlying view is optimized away by SQL Server. It knows that when you say ORDER BY combined with TOP, the ORDER BY is used to determine which rows to include, not which order to present them. When you couple that with TOP 100 PERCENT (which is present in almost every single view), SQL Server says, "oh, you're getting all rows, so I don't need to use the ORDER BY to filter. Cool, I'll find my own way to filter." It throws away the TOP and the ORDER BY. Check plans that reference a view with ORDER BY when the outer query does not - you won't find sort / top operators. – Aaron Bertrand Jul 30 '13 at 20:12