|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Top 100 ... Order by in views SQL 2005 problemignored 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. > I know this is a work around and has some performance implications. But it Weeks of programming?> would allow us to go ahead with the upgrade without 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 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 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 > > > James wrote:
> BTW.. Actually, the documentation states that a "CREATE VIEW statement cannot> > 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. > [...] 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). 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 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). > > James wrote:
Show quote > How long would it take you to make 1000 simple changes in 175000 lines of ORDER BY is not ignored in views. It works as intended for the purposes> 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". > 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 -- >> 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 astandard 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 ... > write code that You are WAY.... off topic there; the whole point to this is that ORDER BY in > depends on proprietary features? 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 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 ... >  |
|||||||||||||||||||||||
Other interesting topics