|
|
Posted by Jim Devenish on 07/06/06 08:46
Erland Sommarskog wrote:
> Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
> purpose. Yes, you may feel that when you do a SELECT on the view
> that you get back the rows in the same order as the ORDER BY clause,
> but that is due to mere chance. Many people who had this sort of
> views found that they no longer the result they expected when they
> moved to SQL 2005.
>
> There is only one way to get an ordered result from a query, and that
> is to add ORDER BY to the query itself.
>
Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQuery had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.
Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQuery Order By AccountName
I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.
Similarly when I look up the syntax of Create View I find, within the
select_statement that:
"A Create View statement cannot include Order By clause, unless there
is also a TOP clause in the select list of the Select statement"
It does not say that this is nonsense.
Navigation:
[Reply to this message]
|