ORDER BY in CREATE VIEW -- sql server programming

Roger Wolter[MSFT]
2/25/2007 6:29:26 PM

Views are by definition unordered. What you're running into is that in
order to correctly evaluate the TOP expression, the optimizer will do a sort
so it can pick the rows to return. Because it does a sort the rows happen
to be returned in sorted order. The optimizer will avoid doing this sort if
possible because the sort is wasted effort because the rows don't have to be
returned in any particular order from a view. In SQL 2000 you were able to
use TOP 100% to force a sort. In SQL 2005, the optimizer is smart enough to
realize that if you specify 100% it can skip the sort. How long do you
think it will be before the optimizer decides that if you ask it to return
more rows than there are in the table, it doesn't have to do a sort? Bottom
line, instead of coming up with a new way to fool the optimizer into doing
an unnecessary sort every release, why not just accept that views are
unordered and write the queries right?

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

[quoted text, click to view]

AdrianDev
2/25/2007 7:36:47 PM

Hi,

I have tables A, B and C that I've joined through a view called MYVIEW . =
In table B there is attribute B.orderby. It is not the primary key.

When I "SELECT * FROM MYVIEW" I always want the results ordered by =
B.orderby. However, an ORDER BY in a CREATE VIEW is not allowed.

Is there any way to achiev this without the ORDER BY. I think the =
default ordering is on the primary key of table A by default.

Erland Sommarskog
2/25/2007 7:52:54 PM

AdrianDev (adrian.owen@btconnect.com) writes:
[quoted text, click to view]

That's the rules of SQL: if you want a certain from our output, you need
to specify an ORDER BY clause, else you are saying "I don't care". Views and
tables are inherently unordered objects. But you can use a stored procedure.

Note: it is possible to use ORDER BY in views if you also use the TOP
clause, and it is common to see views with TOP 100 PERCENT ... ORDER BY...
In SQL 2000, a plain SELECT without an ORDER tend to return result according
to the ORDER BY clause, however this does not equally often in SQL 2005. And
the bottom line remains: a missing ORDER BY means "you are free to return
rows in any order.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Aaron Bertrand [SQL Server MVP]
2/25/2007 8:32:07 PM

[quoted text, click to view]

Great, you managed to save a view to the server that has "ORDER BY" in the
source code.

However, I think you missed the point everyone was trying to make: ORDER BY
when combined with TOP only serves to establish WHICH top n rows you are
talking about. It does *NOT* force the optimizer to return the rows in that
order if you query the view without an ORDER BY clause. So, if you want to
guarantee results in a certain order, you have to append the ORDER BY clause
to the query that asks for data from the view, regardless of whether there
is an ORDER BY clause in the view itself.

A

Erland Sommarskog
2/26/2007 12:00:00 AM

AdrianDev (adrian.owen@btconnect.com) writes:
[quoted text, click to view]

No, it wasn't a resolution. If you want to be dead sure to get back data
in a certain order from your SELECT statement it *must*, I repeat, it *must*
have an ORDER BY clause. If there is no ORDER BY, this means that you
don't care about the order. The ORDER BY in the view is only there to
evaluate the TOP clause, nothing else.

You may think the view is "working", but then there is a new version of
SQL Server, even a new service pack, and you will find is that your
view does "work" any more. The truth is, that the view never worked.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

AdrianDev
2/26/2007 12:06:31 AM

Erland,

I put a "TOP 10000" in the SELECT (its a small table always <100 rows) and
the CREATE VIEW worked.

If you know there is any overhead doing this, please let me know, else thats
thanks for a speedy resolution.

Adrian

[quoted text, click to view]

David Markle
2/26/2007 5:51:18 AM

If you're using SQL Server 2000, when you upgrade to 2005 you will
probably find that your view will come back unordered. It's only an
implementation peculiarity which is causing your view to be "ordered".

[quoted text, click to view]

AdrianDev
2/28/2007 12:35:22 AM

Hi,

I had sent a thank you, but it did'nt get thru?

So thank you

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : ORDER BY in CREATE VIEW