Asger Blond
ab-mi at post3.tele.dk
Sat May 21 04:33:34 CDT 2011
Thanks for the explanation, Stuart. Still I've never seen the result you indicate. The TOP 100 PERCENT is a special case where the query engine will ignore the ordering specified since you are actually requesting all rows. But if you force the engine to make a selection using TOP 99.999999999 PERCENT then it makes no sense to me why the engine should not return the rows in the order it's being forced to use for the selection. And certainly if you use TOP 5 it would be quite inefficient for the engine not to return the ordered rows. So: when the engine is forced to use a condition (TOP 5 or TOP 99.999999999) I've never seen a situation where the rows are not returned in the order specified by the query - whether this is embedded in a view or just run as a plain query. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart McLachlan Sendt: 21. maj 2011 06:29 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] Views don't sort It means that if you specify "SELECT TOP 5 FROM ALPHABET ORDER BY LETTER", you will always get A,B,C,D and E, but they may not necessarily be in that order. They may be returned as "E,D,C,B,A", "D,B,A,E,C" etc. Although it has worked up til now, it is just like any other hack that uses "undocumented features". - after the next patch, hotfix or service pack, you may find that it no longer works that way - the same records could be returned in a completely different sort order, possibly by PK or by the order in which they are physically stored on disk. -- Stuart On 21 May 2011 at 0:09, Asger Blond wrote: > Well, and this quote from BOL just doesn't make any sense to me. The > TOP and ORDER BY clause is used to "determine the rows returned", but > it "does not guarantee ordered results" - WTF does this mean? I use > the construct specified by Francisco, and have never seen problems. > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af > Stuart McLachlan Sendt: 20. maj 2011 22:48 Til: Discussion concerning > MS SQL Server Emne: Re: [dba-SQLServer] Views don't sort > > Be careful with that. It is not guaranteed to work! > > See http://msdn.microsoft.com/en-us/library/ms188385.aspx > <quote> > The ORDER BY clause is not valid in views, inline functions, derived > tables, and subqueries, unless TOP is also specified. ... 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 </quote> > > The only way to be sure is to use "Select * from vwMyView Order by > colMyCol" > > -- > Stuart > > On 20 May 2011 at 13:31, jwcolby wrote: > > > Francisco, > > > > I apparently ignored your top 99.999% part. > > > > When I went back in to my view and selected top 1 million (very big) > > it did in fact return a sorted data set. > > > > Thanks! > > > > John W. Colby > > www.ColbyConsulting.com > > > > On 5/20/2011 1:07 PM, Francisco Tapia wrote: > > > So something like: > > > > > > Create View vwSomeView AS > > > Select TOP 99.9999 percent Field1, Field2, Field3 > > >> From tblSomeTable > > > Order by Field3 > > > > > > does not sort field3? what are your results when you just select > > > * from vwSomeView ? are the results sorted in your results > > > display in management studio? > > > > > > > > > -Francisco > > > <http://bit.ly/sqlthis> > > > > > > > > > > > > On Fri, May 20, 2011 at 10:02 AM, > > > jwcolby<jwcolby at colbyconsulting.com>wrote: > > > > > >> One of the things I am trying to do is use SQL Server to speed up > > >> my applications. The theory is that I can hand off the heavy > > >> lifting to SQL Server and just get back result sets. Of course > > >> this works in terms of joins and filters in a view, but even > > >> though I specify a sort in a view, when the result set hits the > > >> other end (Access in my case) it is unsorted. > > >> > > >> Views have the ability to do sorts, so why is the data returned > > >> by a view into a third party app, or even into another view in > > >> SQL Server unsorted? > > >> Is there a way to tell sql server to return sorted data? > > >> > > >> > > >> > > > _______________________________________________ > > > dba-SQLServer mailing list > > > dba-SQLServer at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > > http://www.databaseadvisors.com > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com