[dba-SQLServer] Order of data in views

Actually, this is by design. And, it is not limited to SSMS.
Using the TOP(99.999999999999999) PERCENT tricks it into
working like we would normally expect.

Robert

At 04:47 AM 5/14/2008, you wrote:
>Date: Wed, 14 May 2008 05:20:32 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Order of data in views
>To: Discussion concerning MS SQL Server
>         <dba-sqlserver at databaseadvisors.com>
>Message-ID: <482AAEE0.8090209 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>Stuart,
>
>  > Enterprise Manager, select the Server, right click and select
>properties,  check the "query timeout" field on the "Connection" tab ?
>
>Query timeout is set to 600 (seconds).  These views are only taking
>~2:40 minutes to run.  I have been down this road before.  Apparently
>that setting is for QUERIES, and apparently the SSMS treats views by a
>different set of rules than queries.
>
>  > Use "SELECT TOP (99.9999999999999) PERCENT..."
>
>Interesting.  Even more interesting, once I did that in one query, the
>rest of the queries sort "properly" even though the underlying view has
>no TOP() statement at all.
>
>Perhaps behind the scenes SSMS is leaving the last used TOP statement
>hanging around (sounds like another bug).
>
>So the takeaway is that it is a "feature" of SSMS that a sorted view
>works at all and don't depend on it.
>
>Just to straighten me out on this though... the theory is that a view is
>"just another table" and tables are by their nature unsorted?  I do
>understand the "tables are by their nature unsorted" thing.  Because a
>view is based on a SQL Statement, I always considered a view "a query"
>(a la Access), which appears to be untrue for whatever reason.
>
>John W. Colby