T-SQL Ordering Myth #2: ORDER BY in Views

, ,

Since a view is a saved query and queries can specify ordering, adding ORDER BY a view definition might seem a reasonable proposition. Try it and Microsoft SQL Server chokes. Then you learn the trick: include a TOP clause and SQL Server will be a-ok with ordering clauses in views. You want all rows returned so you add “TOP 100 PERCENT” to your definition and SQL Server is happy!

Yet you notice something strange. Sometimes the rows returned aren’t sorted according to the view’s ORDER BY clause. As you ponder this puzzlement, your mind wonders back to the roundabout syntax required to finagle ORDER BY into the view definition. Is something funny going on? But the answer eludes you.

“Enough,” you say. “Let’s check the docs and figure this out.” Lo and behold, there on the MSDN page for CREATE VIEW you find an enlightening paragraph:

The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

No wonder SQL Server forbids solo ORDER BY in view definitions! Allowing them would imply that the view’s results will be sorted—and that’s not what happens. ORDER BY in this context is only used in determining which rows match the TOP or OFFSET, not resultset ordering.

“But why not allow it?!” you question. Then you remember that tables and views are interchangeable from the perspective of data retrieval requests. Replacing a view with a table holding the data returned by that view should be transparent to a SELECT query. Since tables don’t guarantee a default sort order, this transparency wouldn’t be possible if views did.

As you walk away from your desk for the day, a moral crystallizes in your thinking: Resultset ordering is controlled exclusively by the data requester. If an ordered resultset is desired, then the client must include ORDER BY in the request it sends to the server.

See also part 1 of T-SQL Ordering Myths: Clustered Indexes Determine Default Sort Order.

Leave a Reply

Your email address will not be published. Required fields are marked *