TOP 100 Percent ORDER BY Considered Harmful.

re: TOP 100 Percent ORDER BY Considered Harmful.

kizzer — Sat, 25 Mar 2006 01:28:10 GMT

Man, that explains a lot!
I've never seen this issue explained so well.
...Off to fix dubiously constructed views!

re: TOP 100 Percent ORDER BY Considered Harmful.

Gaurav Bindlish — Sat, 25 Mar 2006 08:22:38 GMT

Excellent Post .....

I get the part where TOP 100 was ignored. I am curious as to why the ORDER BY clause in the view definition was ignored.

Viste ordinate in SQL Server: trucchi inutili

Marco Russo — Sun, 26 Mar 2006 10:45:31 GMT

Mi è capitato di frequente di vedere l'uso della clausola TOP 100 PERCENT usato nella definizione di...

re: TOP 100 Percent ORDER BY Considered Harmful.

mcdrewski — Thu, 27 Jul 2006 08:29:30 GMT

Trying to create a view which orders (CREATE VIEW blah AS SELECT A,B,C FROM X ORDER BY A) gives : "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

But above you say that if TOP and ORDER BY are specified then ORDER BY is (euphemism alert) "optimised" out. Therefore, I don't understand how this is consistent. The error may as well say "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions", since if it can never be honoured it may as well be illegal.

I guess the SQLServer way to implement data hiding (yes, we use views for this purpose where order is important) is not to use views but to implement all views as stored procedures which do the same thing.

Query Processing -- introduction to the blog!

Tips, Tricks, and Advice from the SQL Server Query Processing Team — Tue, 26 Sep 2006 03:52:20 GMT

Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...

re: [SQL] Le viste ordinate non si fanno!

Andrea Benedetti — Wed, 27 Sep 2006 20:27:41 GMT

Exploring the secrets of intermediate materialization

Adam Machanic — Wed, 04 Oct 2006 09:31:33 GMT

When working with SQL Server 2000, I used to have this little trick I'd pull out after exhausting all...

Exploring the secrets of intermediate materialization

adam machanic :: data manipulation for fun and profit — Wed, 04 Oct 2006 09:31:44 GMT

When working with SQL Server 2000, I used to have this little trick I'd pull out after exhausting all...

Exploring the secrets of intermediate materialization

Adam Machanic — Mon, 08 Jan 2007 22:19:57 GMT

When working with SQL Server 2000, I used to have this little trick I'd pull out after exhausting all

TOP 100 Percent ORDER BY Considered Harmful

SSQA- Users & SQL tools — Fri, 20 Apr 2007 17:42:07 GMT

TOP 100 Percent ORDER BY Considered Harmful Straight to the subject about how it works in SQL 2000 &

More on TOP

Craig Freedman's WebLog — Wed, 01 Aug 2007 19:19:59 GMT

Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP . This week I'll take

No Seatbelt - Expecting Order without ORDER BY

Conor vs. SQL — Thu, 28 Aug 2008 05:46:31 GMT

One of the greatest lessons I've learned in building software is that every technical implementation

Sortierung in View nach Umstellung auf SQL-Server 2005 | hilpers

Sortierung in View nach Umstellung auf SQL-Server 2005 | hilpers — Tue, 20 Jan 2009 17:39:48 GMT

PingBack from http://www.hilpers.com/1005357-sortierung-in-view-nach-umstellung

View ORDER BY lost on web | keyongtech

View ORDER BY lost on web | keyongtech — Thu, 22 Jan 2009 11:41:07 GMT

PingBack from http://www.keyongtech.com/2150129-view-order-by-lost-on

cf_flotsam » Blog Archive » order by clause not working in sql server view

Tue, 28 Apr 2009 05:26:43 GMT

PingBack from http://www.smallbis.com.au/cf_flotsam/2008/11/order-by-clause-not-working-in-sql-server-view/

Tips Tricks and Advice from the SQL Server Query Optimization Team | Paid Surveys

Tue, 02 Jun 2009 09:51:14 GMT

PingBack from http://paidsurveyshub.info/story.php?id=62596

Tips Tricks and Advice from the SQL Server Query Optimization Team | Insomnia Cure

Wed, 10 Jun 2009 03:15:57 GMT

PingBack from http://insomniacuresite.info/story.php?id=6587

Tips Tricks and Advice from the SQL Server Query Optimization Team | debt settlement program

Mon, 15 Jun 2009 20:08:50 GMT

PingBack from http://edebtsettlementprogram.info/story.php?id=24591