Microsoft KB Archive/841845

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

Article ID: 841845

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



SUMMARY
When you use the ORDER BY clause in a view, an inline function, a derived table, or a subquery, you must also specify a Top operator. If you try to use an ORDER BY clause and you do not specify a Top operator, the following error may be returned to the client:

Server: Msg 1033, Level 15, State 1, Line 6

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

When you use an ORDER BY clause in a view, an inline function, a derived table, or a subquery, it does not guarantee ordered output. Instead, the ORDER BY clause is only used to guarantee that the result set that is generated by the Top operator has a consistent makeup. The ORDER BY clause only guarantees an ordered result set when it is specified in the outermost SELECT statement.



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

Keywords: kbinfo kbcode KB841845

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.