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-1bdf3fbde841.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 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
> 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-1bdf3fbde841.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
> >|||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 documentation
> 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/archive/2005/07/20/441053.aspx
Regards :)
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...
> 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
>> 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-1bdf3fbde841.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
>> >|||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:
> 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
> >
> > 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-1bdf3fbde841.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
> > >|||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 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:
> > 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
> > >
> > > 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-1bdf3fbde841.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
> > > >|||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:
> 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 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:
> >
> > > 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
> > > >
> > > > 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-1bdf3fbde841.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
> > > > >|||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:
> 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:
> >
> > 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 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:
> > >
> > > > 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
> > > > >
> > > > > 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-1bdf3fbde841.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
> > > > > >|||> 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...
> 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:
>> 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:
>> >
>> > 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 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:
>> > >
>> > > > 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
>> > > > >
>> > > > > 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-1bdf3fbde841.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
>> > > > > >|||Peter,
When you realize why you can use TOP xxx ORDER BY in a view, you will
understand.
If your view definition does a TOP selection, the ORDER BY will
determine which rows will be part of the result.
If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
my_table ORDER BY my_primary_key_column" and you select from the view,
the query plan will show the TOP operator. The same is true if you
specify SELECT TOP 100.
If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
will be eliminated from the resultset, regardless of any sorting, so it
does not add a TOP operator to the query plan, nor does it see need to
retrieve the table rows in an ordered fashion (unless you add an ORDER
BY clause to the SELECT that selects from the view). So from an
optimizer perspective it makes perfect sense to disregard the TOP 100
PERCENT .. ORDER BY from the view.
HTH,
Gert-Jan
Peter Jones 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:
> > 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:
> >
> > >
> > > 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 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:
> > > >
> > > > > 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
> > > > > >
> > > > > > 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-1bdf3fbde841.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
> > > > > > >|||Hi Gert-Jan,
Thank you for taking the time to add to this thread. I was having trouble
understanding why ORDER BY was even permitted as part of a view - your
comment makes it clear.
Thanks - Peter
"Gert-Jan Strik" wrote:
> Peter,
> When you realize why you can use TOP xxx ORDER BY in a view, you will
> understand.
> If your view definition does a TOP selection, the ORDER BY will
> determine which rows will be part of the result.
> If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
> my_table ORDER BY my_primary_key_column" and you select from the view,
> the query plan will show the TOP operator. The same is true if you
> specify SELECT TOP 100.
> If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
> will be eliminated from the resultset, regardless of any sorting, so it
> does not add a TOP operator to the query plan, nor does it see need to
> retrieve the table rows in an ordered fashion (unless you add an ORDER
> BY clause to the SELECT that selects from the view). So from an
> optimizer perspective it makes perfect sense to disregard the TOP 100
> PERCENT .. ORDER BY from the view.
> HTH,
> Gert-Jan
>
> Peter Jones 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:
> >
> > > 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:
> > >
> > > >
> > > > 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 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:
> > > > >
> > > > > > 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
> > > > > > >
> > > > > > > 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-1bdf3fbde841.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
> > > > > > > >
>sql