[dba-SQLServer] Order of data in views

Robert L. Stewart robert at webedb.com
Wed May 14 07:38:13 CDT 2008


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





More information about the dba-SQLServer mailing list