Behavior of the ORDER BY clause in views, derived tables, inline functions, and subqueries in SQL 2000 (841845)

MORE INFORMATION

Because of the sequence of operations that occurs during query execution, the result sets may seem to be ordered. For example, consider the following view definition:

CREATE VIEW vw_authors 
AS
SELECT TOP 10 PERCENT * 
FROM dbo.authors 
ORDER BY au_fname

Consider the following simple query that uses the view:

SELECT au_fname, state 
FROM dbo.vw_authors
WHERE state = 'CA'

au_fname             state 
-------------------- ----- 
Abraham              CA
Akiko                CA

The order of the output from this SELECT statement seems consistent with the ORDER BY clause that is specified in the view definition. However, the order of the result set is only a by-product of the query plan:

  |--Filter(WHERE:([authors].[state]='CA'))
       |--Top(10 PERCENT)
            |--Sort(ORDER BY:([authors].[au_fname] ASC))
                 |--Clustered Index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]))

A review of the query plan shows why the results seem to be ordered. The first operation that is performed is a Clustered Index Scan to produce all the rows in the authors table. The Clustered Index Scan operation is followed by a sort operation. The sort operation produces a rowset for consumption by the Top operator, and then the output of the Top operator is filtered by using the criteria that are specified in the SELECT statement. In this case, there is no operator above the Sort operator that would change the order of the rowset. The end results appear in the order of the last sort operation.

If you use the same view with a more complex outer SELECT statement, it produces a result set that has no discernible order:

SELECT au_fname, title, royaltyper 
FROM vw_authors
INNER JOIN titleauthor ta ON vw_authors.au_id = ta.au_id
INNER JOIN titles t ON t.title_id = ta.title_id
WHERE royaltyper >=  10

au_fname             title                                                                            royaltyper  
-------------------- -------------------------------------------------------------------------------- ----------- 
Akiko                Sushi, Anyone?                                                                   40
Albert               Is Anger the Enemy?                                                              50
Albert               Life Without Fear                                                                100
Abraham              The Busy Executive's Database Guide                                              60

Again, a review of the query plan shows why the results seem to be ordered:

  |--Hash Match(Inner Join, HASH:([ta].[title_id])=([t].[title_id]), RESIDUAL:([t].[title_id]=[ta].[title_id]))
       |--Hash Match(Inner Join, HASH:([authors].[au_id])=([ta].[au_id]), RESIDUAL:([ta].[au_id]=[authors].[au_id]))
       |    |--Top(10 PERCENT)
       |    |    |--Sort(ORDER BY:([authors].[au_fname] ASC))
       |    |         |--Index Scan(OBJECT:([pubs].[dbo].[authors].[aunmind]))
       |    |--Clustered Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[UPKCL_taind] AS [ta]), WHERE:([ta].[royaltyper]>=10))
       |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))

Hash join operations are performed to join the output of the vw_authors view with the titleauthor table and the title table. Hash join operations do not maintain any particular order, and therefore, the end result set is not ordered.

Note These examples use views, but the information also applies to inline functions, subqueries, and derived tables.

If you want your result set to have a specific order, you must specify that in the outermost SELECT statement. Using the previous example, if you want the result set to be ordered by the au_fname column, you can write the following:

SELECT au_fname, title, royaltyper 
FROM vw_authors
INNER JOIN titleauthor ta ON vw_authors.au_id = ta.au_id
INNER JOIN titles t ON t.title_id = ta.title_id
WHERE royaltyper >=  10
ORDER BY au_fname

This example produces an execution plan that has a sort operation as the last operation. You can also use only operators that preserve the order of a previous sort. One way to preserve the order is to create a plan that uses nested loop joins:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([ta].[title_id]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([authors].[au_id]))
       |    |--Top(10 PERCENT)
       |    |    |--Sort(ORDER BY:([authors].[au_fname] ASC))
       |    |         |--Index Scan(OBJECT:([pubs].[dbo].[authors].[aunmind]))
       |    |--Clustered Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[UPKCL_taind] AS [ta]), SEEK:([ta].[au_id]=[authors].[au_id]),  WHERE:([ta].[royaltyper]>=10) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [t]), SEEK:([t].[title_id]=[ta].[title_id]) ORDERED FORWARD)

You can also use hash match joins, where a sort operation is performed after the joins have finished:

  |--Sort(ORDER BY:([authors].[au_fname] ASC))
       |--Hash Match(Inner Join, HASH:([ta].[title_id])=([t].[title_id]), RESIDUAL:([t].[title_id]=[ta].[title_id]))
            |--Hash Match(Inner Join, HASH:([authors].[au_id])=([ta].[au_id]), RESIDUAL:([ta].[au_id]=[authors].[au_id]))
            |    |--Top(10 PERCENT)
            |    |    |--Sort(ORDER BY:([authors].[au_fname] ASC))
            |    |         |--Index Scan(OBJECT:([pubs].[dbo].[authors].[aunmind]))
            |    |--Clustered Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[UPKCL_taind] AS [ta]), WHERE:([ta].[royaltyper]>=10))
            |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))

You can use stored procedures to provide a server-side method to make sure that the result sets are ordered:

CREATE PROCEDURE usp_orderedauthors(@state varchar(2))
AS
SELECT TOP 10 percent * 
FROM dbo.authors 
WHERE state = @state
ORDER BY au_fname