[dba-SQLServer] Views don't sort

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