Sort order and view

HI all,

Using SQL 2005, I create a simple view
Select * from employee order by name

But when I select the view with
select * from employeeview the sort order is not adheered to. I have to
specificcally issue a order by on the view

Can someone explain this to me

Thanks
Robert

Robert Bravery wrote:
> HI all,
>
> Using SQL 2005, I create a simple view
> Select * from employee order by name
>

Views are like tables - they are unordered sets by definition.

ORDER BY is permitted in a view only when TOP is used. In that case the
purpose of ORDER BY is purely to define the subset of rows returned by
TOP, not to define any sort order (agreed, this is a really stupid and
confusing syntax).

> But when I select the view with
> select * from employeeview the sort order is not adheered to. I have to
> specificcally issue a order by on the view

As with any query, unless you specify ORDER BY the expected result is
logically unordered. The optimizer is free to return the result in any
order at all.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--