On Fri, 1 Mar 2013 15:22:44 +0100, "Parzival" <parzival1969@...> wrote:

> Hello Mark,
>
> can you please give some background on this recommendation?

Specifying an ORDER BY on a view in general makes no sense. Views are not
'named queries' they are predefined sets or 'transient tables' that you can
use in a query as if they are tables. The only exception might be if you
want the view to only return the first x rows. SQL Server even enforces
this: specifying an ORDER BY in a view will is only allowed if you specify
a TOP (x) or TOP (x) PERCENT clause in your query and even then it is not
guaranteed that the output of the view will be ordered, just that it will
contain the TOP (x) rows of the specified order.

And as this example clearly demonstrates using an ORDER BY in a view can
seriously screw up the execution plan when you apply an additional sort
order in the query using the view. My guess is that specifying the ORDER BY
in the view forces the optimizer to materialize the entire view result
before using it in the query instead of optimizing the entire query
including the view.

> ORDER BY only on fields that have an index or not at all?

a) Don't use ORDER BY in a view. Period.
b) For experts: Only use ORDER BY in a view when you really need it (eg to
make the view only contain the first x rows or something), but otherwise:
see a.

Indexes are only interesting if they provably increase the performance of
your queries.

Mark