Error CREATing VIEW containing a UNION ALL and ORDER BY query

Thanks for the comment.  Unfortunately, this results in:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ORDER'.

sorry.,

CREATE VIEW MyView AS
SELECT TOP 100 PERCENT * 
FROM ( SELECT Surname, GivenName FROM Table 1
       UNION ALL
       SELECT Surname, GivenName FROM Table2
     ) as sq
ORDER BY Surname

                                              

1:
2:
3:
4:
5:
6:
7:

OK,thanks.
I tried this but the ordering is still no good.
It is in order, but it gets to Z and then starts again - almost as though it applies the order to each table in the Union.
As a query, this returns the results perfectly.
BUT when this exact same query is put into a View (using Create View etc) the order is all over the place. Interestingly, I did spot this article:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.mseq&tid=ab33638c-97fe-4f12-bd12-e96d68a30a06&cat=&lang=&cr=&sloc=&p=1

OR so I think..

Never mind.  The TOP allows the order by.

acperkins, are you saying that using TOP 99.99999 percent WOULD honour my ORDER BY clause in the View?
FYI: I have worked around this issue now anyway using a long-winded change within the client application (not SQL), so I am not looking to employ "TOP 99.99999 PERCENT"; I am just curious as to whether doing that causes the View to sort as stipulated in the ORDER BY clause.

Is it unfair of me to expect MS to give us a sortable View?
Did MS do this by choice to keep us developers busy?  OR does the issue run deeper than that?  :D

It is intended that you apply your sorting when selecting from your view.  Just like you have no guarantee that your data will be returned from a table in any order without doing an order by, you have to order a view.

Hmmm.  I figured as much but unfortunately one of our old routines relies on the position of a certain row being predictable, and this old routine is outside of our app and beyond our control.  Nevermind. :D
Thanks to all for the posts.

P.S. If you find that TOP 99.99999 is a "solution", I will not be surprised in future, if I see you asking a similar question when you upgrade to SQL Server 2008.

Thanks Dad   :D
Your comment was: "While you may fool SQL Server 2005 by changing it to TOP 99.99999 PERCENT...".
LIKE I SAID, I was not looking to employ this "TOP 99.99999 PERCENT" crap - I was just curious because I read your comment as implying that TOP 99.99999 PERCENT was somehow different from TOP 100 PERCENT in terms of its ability to sort a View.
I may not hold an EE-Genius ranking but even I can spot that "TOP 99.99999 PERCENT" is dodgy, which is why I didn't think of it.  I am, however, into "learning" which is why I asked you to elaborate on your earlier comment.
Thanks.

PS - My comments weren't meant to sling mud at Microsoft (in fact I'm quite the MS fan!); I was simply trying to learn of the reasons why a View would not be sortable or at least by default preserve the order of the first table in the View's FROM clause.

Here is the logic behind why TOP 99.9999999 is supposed to "work".  If you use TOP 100 the SQL Server engine knows that you want all the records and produces them all (minus the sort).  If you use TOP 99.9999999 than it will attempt to only retrieve those records that comply.

As always with undocumented functionality your miles may vary.

Good luck.