Top 100 ... Order by in views SQL 2005 problem

24 Oct 2006 9:30 AM

James

The following Microsoft article says that use of Orderby in views is now
ignored in SQL2005.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

There seems to be a work around:

http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

This involves changing the TOP 100 to TOP 2147483647.

I know this is a work around and has some performance implications. But it
would allow us to go ahead with the upgrade without weeks of programming.
Does anyone have more information on the reliablity of this fix? Does
Microsoft support it?

Thanks,

James.

25 Oct 2006 5:01 AM

Aaron Bertrand [SQL Server MVP]

> I know this is a work around and has some performance implications. But it
> would allow us to go ahead with the upgrade without weeks of programming.

Weeks of programming?

How much of your application could possibly rely on the ability to SELECT *
FROM View and expect a certain order? How much programming time could
really involved with adding ORDER BY x to the SELECT statements that use the
view?

A

25 Oct 2006 7:14 AM

James

BTW..

I checked in books online for SQL 2000 and under "Order by" or "Create View"
there is no mention of this random number generator "feature" of SQLServer.
Rather the opposite: the documentation says if you would like to use Order by
in a view, please remember to put in the Top 100. This implies that it is ok
to do so. I think if something is working at random it should at least be
documented.

Neither have we yet hit a problem with the order by not working in the view
until upgrading to 2005, although I take your point that we may not have
noticed it.

Show quoteHide quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> > I know this is a work around and has some performance implications. But it
> > would allow us to go ahead with the upgrade without weeks of programming.
>
> Weeks of programming?
>
> How much of your application could possibly rely on the ability to SELECT *
> FROM View and expect a certain order? How much programming time could
> really involved with adding ORDER BY x to the SELECT statements that use the
> view?
>
> A
>
>
>

25 Oct 2006 7:45 AM

Damien

James wrote:

> BTW..
>
> I checked in books online for SQL 2000 and under "Order by" or "Create View"
> there is no mention of this random number generator "feature" of SQLServer.
> Rather the opposite: the documentation says if you would like to use Order by
> in a view, please remember to put in the Top 100. This implies that it is ok
> to do so. I think if something is working at random it should at least be
> documented.
>

Actually, the documentation states that a "CREATE VIEW statement cannot
[...] include ORDER BY clause, unless there is also a TOP clause [...]"

That statement was not meant to say "we've put an arbitrary restriction
in, such that you can use the ORDER BY feature, but only if you use
another feature in a non-obvious manner", it was meant to indicate that
the ORDER BY would only make sense if there was a TOP clause (since if
you're specifying TOP <n>, it needs an order to determine what is at
the "top").

However, unfortunately, down the years, someone discovered that a hack
that seemed to work was to ask for TOP 100 PERCENT, which would then
allow the ORDER BY to be included, and it seemed to work. And thus was
born this faulty habit of believing that you could create ordered
views...

Nowhere that I can find in BOL for 2000 does it say that you should use
ORDER BY with TOP 100 PERCENT.

Damien

PS - I too find it hard to believe that it's going to take weeks to
find all uses of this view and update appropriately. In the time that
this discussion has spread over, you should have been able to do a find
in files or similar operation to find all references to the view(s) and
update the queries appropriately. And you'd then know that this
particular area has been resolved once and for all (rather than you
knowing that the area has to be revisited, but the business not giving
you time to do that since it's now working).

25 Oct 2006 8:14 AM

James

How long would it take you to make 1000 simple changes in 175000 lines of
code? Plus test and compile etc.

The books on line should say "Do not use order by". It implies the oppisite.

The exact text in BOL is:

>>>>>>>>>>>>

There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
....

Include ORDER BY clause, unless there is also a TOP clause in the select
list of the SELECT statement.
<<<<<<<<<<<<

Perhaps it should say "Order by clause is ignored".

Show quoteHide quote

"Damien" wrote:

> James wrote:
> > BTW..
> >
> > I checked in books online for SQL 2000 and under "Order by" or "Create View"
> > there is no mention of this random number generator "feature" of SQLServer.
> > Rather the opposite: the documentation says if you would like to use Order by
> > in a view, please remember to put in the Top 100. This implies that it is ok
> > to do so. I think if something is working at random it should at least be
> > documented.
> >
>
> Actually, the documentation states that a "CREATE VIEW statement cannot
> [...] include ORDER BY clause, unless there is also a TOP clause [...]"
>
> That statement was not meant to say "we've put an arbitrary restriction
> in, such that you can use the ORDER BY feature, but only if you use
> another feature in a non-obvious manner", it was meant to indicate that
> the ORDER BY would only make sense if there was a TOP clause (since if
> you're specifying TOP <n>, it needs an order to determine what is at
> the "top").
>
> However, unfortunately, down the years, someone discovered that a hack
> that seemed to work was to ask for TOP 100 PERCENT, which would then
> allow the ORDER BY to be included, and it seemed to work. And thus was
> born this faulty habit of believing that you could create ordered
> views...
>
> Nowhere that I can find in BOL for 2000 does it say that you should use
> ORDER BY with TOP 100 PERCENT.
>
> Damien
>
> PS - I too find it hard to believe that it's going to take weeks to
> find all uses of this view and update appropriately. In the time that
> this discussion has spread over, you should have been able to do a find
> in files or similar operation to find all references to the view(s) and
> update the queries appropriately. And you'd then know that this
> particular area has been resolved once and for all (rather than you
> knowing that the area has to be revisited, but the business not giving
> you time to do that since it's now working).
>
>

25 Oct 2006 9:06 AM

David Portas

James wrote:
Show quoteHide quote

> How long would it take you to make 1000 simple changes in 175000 lines of
> code? Plus test and compile etc.
>
> The books on line should say "Do not use order by". It implies the oppisite.
>
> The exact text in BOL is:
> >>>>>>>>>>>>
> There are a few restrictions on the SELECT clauses in a view definition. A
> CREATE VIEW statement cannot:
> ...
>
> Include ORDER BY clause, unless there is also a TOP clause in the select
> list of the SELECT statement.
> <<<<<<<<<<<<
>
> Perhaps it should say "Order by clause is ignored".
>

ORDER BY is not ignored in views. It works as intended for the purposes
of defining the selection criteria used by the TOP operator. There is
no particular reason not to use ORDER BY in views because by definition
it has to be used in conjunction with TOP. I will accept that the TOP
feature is extremely poorly designed. The fact that TOP implies a
double meaning for the ORDER BY clause creates a number of problems.

Perhaps the documentation could have said explicitly that views are
unordered. However, Books Online is not intended as a tutorial.
Throughout, the docs assume a familiarity with the basics of relational
databases and SQL. One of the most fundamental features of relational
systems is that relations (tables and views) are unordered sets.

Also, the fact that you started this thread shows that putting
something in writing doesn't solve the problem. In SQL Server 2005
Microsoft has made it absolutely explicit that the ordering of queries
against views is undefined unless you specify ORDER BY in the query.
They have even provided an example to demonstrate the fact. Yet some
people still doubt what they read and continue to speculate about the
reliability of an undocumented "feature".

--
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
--

25 Oct 2006 3:33 PM

--CELKO--

>> There is no particular reason not to use ORDER BY in views because by definition it has to be used in conjunction with TOP. I will accept that the TOP feature is extremely poorly designed. <<

With SQL-2005, he can use ROW_NUMBER() OVER (ORDER BY ..) and have a
standard syntax which is not subject to the next service pack changes.
But I have to agree with everyone who posted a reply. How vcan people
not know even the most basic concepts of RDBMS or write code that
depends on proprietary features? It has been over 30 years since Codd
wrote his stuff ...

25 Oct 2006 8:46 PM

Tony Rogerson

> write code that
> depends on proprietary features?

You are WAY.... off topic there; the whole point to this is that ORDER BY in
view giving back an ordered result set is and has never been documented in
books online; its not a feature per se, its a behaviour that people have
noticed, been blogged about and started to be used.

People rely on it but its not documented, just like a few other things
assignment concatenation is another.

Using documented proprietary features is NOT I say NOT a problem.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quoteHide quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1161790400.935702.43850@k70g2000cwa.googlegroups.com...
>>> There is no particular reason not to use ORDER BY in views because by
>>> definition it has to be used in conjunction with TOP. I will accept
>>> that the TOP feature is extremely poorly designed. <<
>
> With SQL-2005, he can use ROW_NUMBER() OVER (ORDER BY ..) and have a
> standard syntax which is not subject to the next service pack changes.
> But I have to agree with everyone who posted a reply. How vcan people
> not know even the most basic concepts of RDBMS or write code that
> depends on proprietary features? It has been over 30 years since Codd
> wrote his stuff ...
>