[dba-SQLServer] Views don't sort

Francisco Tapia fhtapia at gmail.com
Fri May 20 19:26:48 CDT 2011


John,
  We've been using the top 99.99999 method in house and while we do
test our sp patches, we have not had any issues with that, but to try
to avoid an unholy war, it would be good for you to figure out how to
implement passthrough queries from access, it's generally the better
way to link to views and sprocs on a server.

Calling a view/Sproc in a passthrough query is just like banging out
the request in a SQL query window

Ie, select * from viewMyView

Or exec stp_myFavoriteSproc param1, param2


Sent from my mobile

On May 20, 2011, at 4:01 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:

> I understand that.  Unfortunately to do it "the safe way" means that it has to be done by the Jet engine on the workstation.  That means back to all of the index pulls etc.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 5/20/2011 4:47 PM, Stuart McLachlan wrote:
>> 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"
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>



More information about the dba-SQLServer mailing list