[dba-SQLServer] Order of data in views

Martin W Reid mwp.reid at qub.ac.uk
Wed May 14 04:47:01 CDT 2008


John

I always treated a view as a virtual table created at the point the statement is executed. The use orderby on the statement hitting the view.

Martin


Martin WP Reid
Information Services
Queen's University
Riddel Hall
185 Stranmillis Road
Belfast
BT9 5EE
Tel : 02890974465
Email : mwp.reid at qub.ac.uk
________________________________________
From: dba-sqlserver-bounces at databaseadvisors.com [dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com]
Sent: 14 May 2008 10:20
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Order of data in views

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
www.ColbyConsulting.com


Stuart McLachlan wrote:
> On 13 May 2008 at 16:34, jwcolby wrote:
>
>> Well, we are talking about a GroupBy Cnt query here.  The GroupBy field
>> is what is ordered on, and is specifically ordered on that field in the
>> underlying view.
>>
>> Unfortunately I cannot execute the view since the view takes about 40
>> seconds to complete and MS times out in 30, which no one here has
>> managed to show me how to prevent.
>>
>
> Enterprise Manager, select the Server, right click and select properties,  check the "query
> timeout" field on the "Connection" tab ?
>
>
>> Therefore I am forced to create a query and paste the query into that in
>> a SELECT * FROM SomeView SQL statement.  Given that the view is ordered,
>> you (I) would expect a query directly based on that to be ordered unles
>> specifically reordered in some manner.  Not so for whatever reason.
>>
>
> Use "SELECT TOP (99.9999999999999) PERCENT..."
> see
> http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html
>
>
> _______________________________________________
> 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




More information about the dba-SQLServer mailing list