ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde84
1.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:

> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be th
e
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde
841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
>|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:
CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName
Then query the view:
SELECT * FROM TopView
Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:
SELECT * FROM TopView
ORDER BY LastName
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
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
Regards
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
> Cheers, Peter
> "John Bell" wrote:
>|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDE
R
> BY in a view definition"
> John
> "Peter Jones" wrote:
>|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the co
de
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
>|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905
-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...[vbcol=seagreen]
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>