Ordering guarantees of queries in various context is a common source of confusion. For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent to the client since the query optimizer will re-order operations to find more efficient query plans. Note that even though this topic applies to SQL Server 2005 most of the rules are valid for SQL Server 2000 too.
 
Here are the scenarios that guarantee ordering:
  1. If you have an ORDER BY in the top-most SELECT block in a query, the presentation order of the results honor that ORDER BY request
  2. If you have a TOP in the same SELECT block as an ORDER BY, any TOP computation is performed with respect to that ORDER BY. For example, if there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows within a given sort.  Note that this does not guarantee that subsequent operations will somehow retain the sort order of a previous operation. The query optimizer re-orders operations to find more efficient query plans
  3. Cursors over queries containing ORDER BY in the top-most scope will navigate in that order
  4. INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
  5. SQL Server 2005 supports a number of new "sequence functions" like RANK(), ROW_NUMBER() that can be performed in a given order using a OVER clause with ORDER BY
  6. For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.
--
Conor Cunningham
Query Optimizer Development Lead